Need to show last year data in clustered bar chart that dynamically show last N months

[TanawatP]

Hi, I have clustered bar chart that dynamically showed 12 months data based on filter by using following DAX formula.

I need to have second bar chart to show same month last year for comparison and cannot find any DAX that works for this.

I have tried SAMEPERIODLASTYEAR and PARALELLPERIOD, but it did shot seems to work. Can anyone please help me on this?

Thank you!

[SBIM]

Not sure if this is still an issue for you, but it was for me and this was my solution. I created a YoY quick measure then converted it to be a sum of the prior year.

RevenuePY =

IF(

    ISFILTERED(MyTable[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            [Revenue],

            DATEADD(MyTable[Date].[Date], -1, YEAR)

        )

    RETURN

        __PREV_YEAR

)

Note [Revenue] is just a seperate measure doing the sum of revenue, you could calculate it directly in the formula if needed.

[TheoC]

Hi @TanawatP 

Hope this is what you're after?  Just an FYI, I created two measures as per below:

CurMthAmount = CALCULATE ( [Total Amount] , DATESMTD ( Table1[Date] ) )

LastPeriod = CALCULATE ( [Total Amount] , DATEADD ( 'Table1'[Date] , -1 , YEAR ) )

Interestingly, I had to first create a Table with Date, Current Period (2021) and Prior Period (2020) and I then converted the table to a Clustered Column Chart ( as below). For some reason, Power BI did not present the LastPeriod measure I created (above) on the Clustered Column Chart.  I have no idea why but I assume it is one of two things: a user error .... or a glitch... Given I have done it a few times before no hassles, I am going to go out and say that it's likely a glitch...

Hope this helps! 

Theo 🙂

[TanawatP]

Thank you for your response, I tried it but the number of months did not change dynamically based on filter selection. i.e. If I have data from Jan 2019 to Oct 2021. When I select filter on Sep-2021, I expect the chart to show data only from Oct-2020 to Sep-2021 only.

You can refer to my DAX in the first screenshot. Thank you