Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VickyDev18
Helper II
Helper II

Prior Year Value not calculating correctly

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

 

VickyDev18_1-1718812042444.png

 

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

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.