cancel
Showing results for 
Search instead for 
Did you mean: 
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

 

2 REPLIES 2
TheoC
Memorable Member
Memorable Member

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.