cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothschild
Helper V
Helper V

Generic period over period

Is there a way to create a drill-down clustered column chart so that it can show "Year versus Last Year", "Quarter versus Last Quarter", or "Month versus Last Month", as I drill down?  Said differently, I only want two periods shown: Current and Last, and want them displayed as Year, Quarter, or Month depending on the drill-down choice.

 

Sample data posted below.

 

Thanks!

 

 

DateValuezzValue Last YearzzValue Last MonthzzValue Last Quarter
3/1/18$0   
4/1/18$0 $0 
5/1/18$0 $0 
6/1/18$0 $0$0
7/1/18$0 $0$0
8/1/18$17,692 $0$0
9/1/18$32,246 $17,692$0
10/1/18$24,740 $32,246$0
11/1/18$16,553 $24,740$17,692
12/1/18$16,543 $16,553$32,246
1/1/19$16,865 $16,543$24,740
2/1/19$441,567 $16,865$16,553
3/1/19$16,567$0$441,567$16,543
4/1/19$274,838$0$16,567$16,865
5/1/19$16,576$0$274,838$441,567
6/1/19$16,567$0$16,576$16,567
7/1/19$2,708,003$0$16,567$274,838
8/1/19 $17,692$2,708,003$16,576
9/1/19 $32,246 $16,567
10/1/19 $24,740 $2,708,003
11/1/19 $16,553  
12/1/19 $16,543  
1/1/20 $16,865  
2/1/20 $441,567  
3/1/20 $16,567  
4/1/20 $274,838  
5/1/20 $16,576  
6/1/20 $16,567  
7/1/20 $2,708,003  
1 ACCEPTED SOLUTION
jdbuchanan71
Super User II
Super User II

Hello @mrothschild 
It is, just by checking the layer you are working with, along these lines:

Previous Period = 
IF ( ISINSCOPE ( 'Date'[Calendar Year Month] ), CALCULATE ( [Sales Amount], DATEADD('Date'[Date],-1,MONTH) ),
IF ( ISINSCOPE ( 'Date'[Calendar Year Quarter] ), CALCULATE ( [Sales Amount], DATEADD('Date'[Date],-1,QUARTER) ),
IF ( ISINSCOPE ( 'Date'[Calendar Year] ), CALCULATE ( [Sales Amount], DATEADD('Date'[Date],-1,year) ),0)))

PreviousPeriod.jpg 

 

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

View solution in original post

4 REPLIES 4
mrothschild
Helper V
Helper V

Thanks everyone!  The ISINSCOPE() did the trick!

 

mwegener
Super User II
Super User II

Hi @mrothschild 

 

look at this.

https://www.daxpatterns.com/time-patterns/#complete-period-comparison-patterns

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


parry2k
Super User III
Super User III

@mrothschild you can surely do that, create a measure with ISINSCOPE function to find out which level you are at and then pick respectice measure.

 

Read more about ISINSCOPE here






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





jdbuchanan71
Super User II
Super User II

Hello @mrothschild 
It is, just by checking the layer you are working with, along these lines:

Previous Period = 
IF ( ISINSCOPE ( 'Date'[Calendar Year Month] ), CALCULATE ( [Sales Amount], DATEADD('Date'[Date],-1,MONTH) ),
IF ( ISINSCOPE ( 'Date'[Calendar Year Quarter] ), CALCULATE ( [Sales Amount], DATEADD('Date'[Date],-1,QUARTER) ),
IF ( ISINSCOPE ( 'Date'[Calendar Year] ), CALCULATE ( [Sales Amount], DATEADD('Date'[Date],-1,year) ),0)))

PreviousPeriod.jpg 

 

If this solves your issues please mark it as the solution. Kudos 👍 are nice too.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - July 22, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors