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

[Bickers1]

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

 

[GraH]

Have you tried using TOTALYTD? See TOTALYTD function (DAX) - DAX

 

[Bickers1]

Have you tried using TOTALYTD? See TOTALYTD function (DAX) - DAX

Hi Grah, Thanks for the quick reply. I actually figured out that it was a relational issue in my Data Model. ?For the suggestion though.

 

[GraH]

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.