A single measure for different timelines

[howdy123]

Hey,

I've been trying to resolve this issue for long - and its resolution makes the difference between a smooth reporting and a tough one.



I created a bunch of measures and two chronologies. I'd like to calculate these measures for different timelines.



If I create two columns in PowerPivot : "if( and( date >= firstdate(...) ; "date <= lastdate(...) ; "Timeline1; Blank() )" [and the same for date2] and put them as column filters in a pivot table, I get the usual :


(one below the other)


I am unable to create a single column that takes either the value 'Time range 1' and 'Time range 2'... and even if I could, I guess I would have issues if the datas were mixed (eg. timeline 1 is June and timeline 2 is YtD June, June would appear either as timeline1 or timeline2, but not as both).


I guess this is a pretty standard issue but never managed to resolve it. Could you please me indicate the smoothest way to resolve this ?


Best.

 

[Matt Allington]

Calculated columns do not respond to slicers. So that won't work. You effectively have 3 measures with 2 different selectable time periods - so 6 different measures. Have you considered using cube formulas? Click in your pivot, select analyse (I think), OLAP Tools, convert to formulas. You can then layout the measures any way you want to

 

[howdy123]

Hey Matt, thank you for your answer !

So there's no perfect solution for this. What you proposed is actually what I did so far, ie. putting Values in lines and creating measures for :
Cars built per factory Time1
Cars built per factory Time2
Cars / hours Time1
Cars / hours Time2
etc.

I tried using Cube formulas yesterday (thanks to your book), but performance was low (~3 sec to refresh ?!!). So I guess I won't sacrifice performance, and continue the old way.


Many thanks.

 

[Matt Allington]

Interesting that 6 cube formulas are materially slower than the pivot table. Another approach is you could creat the pivot table, and then use get pivotdata to put the answers in cells. Kind of like cube formulas but with a single pivot table query. I have never used nor liked get pivot data, but it might kill 2 birds here

 

[howdy123]

That's what I do, it's less error prone that directly aiming at a pivot cell.

Thank you.