Forum Discussion
Power pivot output with overlapping periods
Hi,
I have a dataset containing monthly transaction data broken down by transaction type which could take on any value from Transaction A, B, C ... X.
In a separate table I have defined a number of periods like this:
Connecting these two tables through the Month column in the datamodel I can then summarise the value of each transaction type by period in a pivot table:
What I would like to achieve however, is a pivot table output of this structure:
...where the LTM periods overlaps the FY periods.
Can this be achieved and what would be the easiest way to go about it?
Hi olividd,
Acccording to your description, you could create measures for each column like below to achieve this goal
16/17 = CALCULATE(SUM(T1[value]), FILTER(ALLEXCEPT(T1,T1[Transaction tytpe]), RELATED(t2[fy])="16/17")) ltm 17 = CALCULATE(SUM(T1[value]), FILTER(ALLEXCEPT(T1,T1[Transaction tytpe]), RELATED(t2[ltm])="ltm 17"))
You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
daxCommunity Support
Hi olividd,
Acccording to your description, you could create measures for each column like below to achieve this goal
16/17 = CALCULATE(SUM(T1[value]), FILTER(ALLEXCEPT(T1,T1[Transaction tytpe]), RELATED(t2[fy])="16/17")) ltm 17 = CALCULATE(SUM(T1[value]), FILTER(ALLEXCEPT(T1,T1[Transaction tytpe]), RELATED(t2[ltm])="ltm 17"))
You could refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.