SCD1 and power pivot

[Ffitzpatrick47]

I have a table that looks like this

person  stage1  stage2  stage3

1              jan       mar     jun

2             jan        april    july

I can measure the number of people in a given stage on a given date like this

stage1count = calculate(counta([person]),filter(all(stage1,stage2,stage2),fakedate>stage1&&fakedate<stage2)))

stage2count = calculate(counta([person]),filter(all(stage1,stage2,stage2),fakedate>stage2&&fakedate<stage3)))

where fakedate is the max date of the filter context date.  If you assume that stage 1,2,3 always succeed the prior state.

Then I can do a table like

           jan feb mar april may june july

stage1 2     2     1     0      0      0     0

stage2 0    0      1     2      2      1     0

stage3 0    0      0     0     0       1     2

Of course, this can't be how excel was designed because then I'd have to make measures for every single scd1 measure I've got and then Id' never be able to pivot, explode and implode.  How do I make a column value in the underlying rows change value based on the filter context.  I need something like if([date from filter context]<stage1,stage1,if ... so that I can just drag that new colunm in to the pivot and pivot accordingly without having to machine every measure.

[v-jiascu-msft]

Hi @Ffitzpatrick47,

The calculated column won't response to the filters (including slicers). I'm afraid the calculated column won't help. Can you share a sample? What's the "filter context date" for?

Best Regards,

Dale

[Ffitzpatrick47]

The filter context date is for any time series report.  For example, if I want to show the growth in customers by geography, over time and accomodate the fact that customers move from one state to another.  For example, I moved to California from NY in 2016.  If I was a customer, with scd data, I'd count as a customer in NY in 2014 and California today.  If instead, I used flat data with today's customer dimension against the dates, I'd be a customer in California in 2014 and today.  This is even more importnat for things like sales pipeline maturation or account manager vs revenue growth over time.  Again the critical components is that it's over time and that the dimensions are changing

[v-jiascu-msft]

Hi @Ffitzpatrick47,

Can you share a sample?

Best Regards,

Dale