delta in matrix carryover

[Mihail_C]

Hello community,

I have been trying to figure out how to display the delta between two values accross several months but i cant seem to find the right way to do it.

I have a matrix with a campaign value that has a number of units planned to be bought and several purchases of units during the following months. I can display correctly the values for each month where there is activity, but when it comes to Delta, i would like the value to be carried over month over month, as described in this screenshot.

Basically the units planned should be displayed just under the month when the PeriodID (attached dataset bellow) indicates, ordered units should behave the same, and delta between ordered and planned should dinamically be calculated from month to month so i can more easily track if the planned units have been ordered on time or not.

E3 = D3-C3;  H3 = E3-G3; K3 = H3-J3 so on and so forth

https://drive.google.com/file/d/1ENAdwEykysaFrpjw9MgqcVl_j81isaEl/view?usp=sharing

https://docs.google.com/spreadsheets/d/1rikN1vxz0HXObeYHPFcQUDFkuFITHZCi/edit?usp=sharing&ouid=11800...

Any idea? I've read about isinscope and tried a few times to fiddle with it but atm i cant figure out how to make it show me what i need.

Any support is greatly appreciated.

Thank you

[Mihail_C · Accepted solution]

I went with creating two new measures :

asset_ordered_YTD = TOTALYTD([asset ordered], 'Calendar'[Date])

asset_planned_YTD = TOTALYTD([asset planned], 'Calendar'[Date])

and delta is now the difference between asset_ordered_YTD  and asset_planned_YTD and is behaving nicely. 

Think this topic can be closed. 

Thanks 

[v-shex-msft]

Hi @Mihail_C ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Mihail_C]

Thank you, Amit!

This takes me closer to the desired result indeed. Problem is that i dont want to display the months on a continuum. FY starts in Oct and ends in Sept and i can add a slicer to display just a fiscal year and check the progress of each campaign during that FY.

Campaigns dont stretch on multiple fiscal years, but they can span through 2 calendar years - starting in oct and ending in may next year.

I think it would be better just to display the Delta starting with the Planned month until the last month in which we have Ordered items instead.

[amitchandak]

@Mihail_C , You can create cumulative measures. Also, use month year in the visual not month

delta = CALCULATE([ordered]-[planned], filter(ALLSELECTED('calendar'),'calendar'[Date] <= max('calendar'[Date])))

File is attached filter signature