Projection of variation based on the average of a measure of past months

[Syndicate_Admin]

Community of experts: I come to you for help. I am presented with the following case:

I must generate a matrix where two years must be placed to compare and take out the variation between them. These years are at the user's choice through two segmenters. So far everything is resolved but the point is in the months not elapsed of the current year. A projection of the monthly variation must be made considering the average variation of the months elapsed in the year. That average is applied to the value of the amount of the comparative year to obtain the amount of the future months of the current year. I attach an example: The months painted in green are those already elapsed that give an average variation of -59%. To get the number of events for future months, I must apply - 59% to the number of events for the same month in 2021. The first problem I encounter is that you can't apply the average measure to the variation measure. So, how could you do to do the average calculation. The number of events arise from a table where all events are recorded daily (some of its columns are date and quantity) but this table does not consider future months since they are records of the past and present.

On the other hand, for the current month, the actual variation for the elapsed days of the month and the projected variation for the rest of the days must be considered. And add both to reach the total. Attached example

How would they do it? Thanks a lot

[Syndicate_Admin]

Excellent solution: I only had something left to resolve. Be able to replace the column of the year 2021 (column of the table that is dynamic) with the one that is selected in the segmenter of the comparative year. Can you think of how to do it?

Thanks a lot!

[v-cgao-msft]

Hi @Syndicate_Admin ,

Please refer to the PBIX file:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data