topic Re: Prior Year Value not calculating correctly in DAX Commands and Tips

Prior Year Value not calculating correctly

VickyDev18 — Wed, 19 Jun 2024 15:56:08 GMT

Can anyone help explain why the Amount_PY measure is not returning the correct value in the Full Year scenario below.

It is summing up amount across all years but I'm just not able to figure out why.

DEFINE MEASURE __Metrics[Amount] = SWITCH ( SELECTEDVALUE ( __Period[Period Name] ), "Month", CALCULATE ( SUM ( Financials[Accounting Currency Amount Signed] ), DATESMTD ( 'AccountingCalendar'[Date] ) ), "Year-To-Date", CALCULATE ( SUM ( Financials[Accounting Currency Amount Signed] ), DATESYTD ( 'AccountingCalendar'[Date] ) ), "Full Year", CALCULATE ( SUM ( Financials[Accounting Currency Amount Signed] ), ALLEXCEPT ( AccountingCalendar, AccountingCalendar[Year] ) ), SUM ( Financials[Accounting Currency Amount Signed] ) ) MEASURE __Metrics[Amount_AC] = CALCULATE ( [Amount], Scenarios[Scenario Type] = "Actuals" ) MEASURE __Metrics[Amount_PY] = CALCULATE ( [Amount_AC], SAMEPERIODLASTYEAR ( AccountingCalendar[Date] ) ) EVALUATE SUMMARIZECOLUMNS ( AccountingCalendar[Year], AccountingCalendar[Calendar Month], __Period[Period ID], __Period[Period Name], TREATAS ( { "Latest" }, AccountingCalendar[Month Type] ), TREATAS ( { 2022, 2023, 2024 }, AccountingCalendar[Year] ), "Amount_AC", [Amount_AC], "Amount_PY", [Amount_PY] ) ORDER BY AccountingCalendar[Year] ASC, __Period[Period ID] ASC

Note: The Month Type essentially filters the latest completed month within each year.

Re: Prior Year Value not calculating correctly

amitchandak — Wed, 19 Jun 2024 16:10:15 GMT

VickyDev18 , you also need a switch for the previous measure

Please refer example measure, which you have use in Switch for PY

This will work for the month and the same period

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Last year ytd

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Last year full
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last Year full = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Prior Year Value not calculating correctly

VickyDev18 — Thu, 20 Jun 2024 12:15:42 GMT

Thanks amitchandak . Was able to get it to work after adding SWITCH for the PY measure as well.

However, curious to know why just using SAMEPERIODLASTYEAR does not work in my orginal PY measure.