Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

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

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
belmassiccioni_0-1663190013776.png

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

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
Community Support
Community Support

Hi @Syndicate_Admin ,

 

Please refer to the PBIX file:

vcgaomsft_0-1663237085358.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.