Forum Discussion

VickyDev18's avatar
VickyDev18
Advocate II
2 years ago
Solved

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

 

 

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

 

  • 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

2 Replies

  • 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

    • VickyDev18's avatar
      VickyDev18
      Advocate II

      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.