Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey guys, this is a step beyond my last question so I thought I'd create a new post.
My last post was: Rolling X Month Trend Line
So thanks to the article that was in that post I was able to get this to work with simple sums. IE: Sum of Sales.
However, my needs vary witht his and one of those needs is to create a more complex calculation. I want to show what is essentially a sales turnover calculation that has rolling 3 month averages/sums within it.
So My calculation would be something like this: (Rolling 3 Month SUM COGS)/ (3 Month Inventory Avg)
Currently my issue is I can't get either the numerator or denominator to show enough data. My calculation will only show a couple months of data, and not do a rolling sum. Below is my attempt at just getting a numerator to work:
Rolling X Monthly % (last n months) = VAR MaxFactDate = CALCULATE ( MAX ( Date[MeasureDate] ), ALL ( 'DIMCalendar' ) ) -- ignore the selected date filter, and find the max of date in Sales table VAR FDate = ENDOFMONTH ( 'DIMCalendar'[DateKey] ) -- get the last day of the month selected in the date filter VAR Edate = EDATE ( FDate, -[N Value] ) -- get the last day of -N months VAR 3MonthRolling = CALCULATE ( 'FACT'[Total COGS], DATESINPERIOD ( 'DIMCalendar'[DateKey], LASTDATE ( ('DIMCalendar'[DateKey] ) ), -3, MONTH ) ) VAR NUMERATOR = ( 3MonthRolling*10/2) RETURN IF ( MaxFactDate <= MAX ( 'DIMCalendar'[DateKey] ) && MaxFactDate > Edate, CALCULATE ( IF ( ( NUMERATOR) = BLANK (), 0, ( NUMERATOR)) , ALL ( 'DIMCalendar' ) ) )
Solved! Go to Solution.
Thanks for the reply but I actually found another post that matched what I wanted to do and the solution for it fixed my problem.
hi, @Anonymous
Please share your sample pbix or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading. it will be a better understanding of your requirement.
Best Regards,
Lin
Thanks for the reply but I actually found another post that matched what I wanted to do and the solution for it fixed my problem.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |