Generic period over period

[mrothschild]

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!

Date	Value	zzValue Last Year	zzValue Last Month	zzValue 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

[mrothschild]

Thanks everyone!  The ISINSCOPE() did the trick!

[mwegener]

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.

[parry2k]

@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

[jdbuchanan71 · Accepted solution]

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

 

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