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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TanawatP
New Member

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

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

TanawatP_0-1634387213864.png

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!

TanawatP_1-1634387414197.png

 

3 REPLIES 3
SBIM
Helper I
Helper I

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.
SBIM_1-1671117192993.png

 


 

TheoC
Super User
Super User

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...

 

TheoC_0-1634537409634.png

 

Hope this helps! 

Theo 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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