Power Query/Pivot P&L- Current Period and Period YTD Values are the same.

Bickers1

New Member
Joined
Dec 26, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Merry Xmas to all. Thanks for accepting me. I'm at my wits end with this problem, with a deadline for my Profit & Loss report fast approaching. Any help would be greatly appreciated.

I have imported 316,000 line of data into my Excel data model (Query Appended18to21V1) and also constructed a number of other tables (with relationships in the data model), to drive the correct split of P&L line items and also several date table variations, which I'm using to slice the time/date, to get the financial results for a year/month combo.

I'm using DAX measures to get various combos of information, but for some reason my Month YTD measure produces exactly the same result as my Current Month measure. I'm working on a calendar year Jan-Dec, so say in March I would like to see the result for March and also the total result for Jan-March.

I read a 2011 article, which discussed the use of DAX Measures in order to generate a P&L, the measures I have used are as follows;

Current Month (Which works)

=CALCULATE(SUM(Appended18to21V1[Value]),DATESBETWEEN(Dates[Date],Year_Period[Selected_Month_Start_Date],Year_Period[Selected_Month_End_Date]))
=LASTDATE(Year_Period[Month_Start_Date])
=LASTDATE(Year_Period[Month_End_Date])

Current Month YTD

=CALCULATE(SUM(Appended18to21V1[Value]),DATESBETWEEN(Dates[Date],Year_Period[Selected_Year_Start_Date],Year_Period[Selected_Month_End_Date]))
=LASTDATE(Year_Period[Year_Start_Date])
=LASTDATE(Year_Period[Month_End_Date])

I've checked the outputs of the the LASTDATE measures and they do produce the correct date output, according to the year/month combo I select on my slicers.

I'm new to DAX/Power Query, so please be gentle. I'm hoping that there is a simple solution from someone out there.

Many thanks

Stuart
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
The datamodel can play tricks with you, certainly when one is learning the principles of DAX, relationships and contexts. Nice job for finding it out yourself.
Glad you like the tip.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top