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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

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
Top