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.
I understand that there have been some articles regarding this issue, however, none of them seem to work for me. Would like some help specifically to my situation/data.
Basically I have a table with a few columns. Date, country, size. I would like to calculate the 7 day moving average for a country's size
(E.g. Libya's size). However, Libya does not have size data for all the dates, as shown in the picture below. The moving average formula I used, as shown in the other picture, includes the days where Libya does not have data. What formula should I be using if I want the moving average to consist of 7 non-zero days?
Thanks for the help.
Please try this measure expression in a table or matrix visual with the Date and Country columns in it.
MA7 =
VAR thisdate =
MIN ( Test[Date] )
RETURN
CALCULATE (
AVERAGE ( Test[Size] ),
ALL ( Test[Date] ),
TOPN (
7,
FILTER ( ALL ( Test[Date] ), Test[Date] <= thisdate ),
Test[Date], DESC
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat ,
It does not seem to work. Below are the results in PowerBI (Date, Country, Size, MA7), vs. what I calculated manually via Excel.
Thanks!
@Anonymous , Try @mahoneypat solution. If it does not work out.
Can you share sample data and sample output in table format?
I put in your sample data and tried that measure out, and got the results below that match with what I calculate in Excel too. It is just the MA7 of the Size column (no extra math beyond that). Please reply if you figure out the difference in what we are doing.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat ,
Yeah yours looks great. Not sure why it doesnt work on mine, maybe because of the different filters on size? I'll let you know if i figure it out thanks!
Hi @amitchandak ,
I have done @mahoneypat 's method, and have the results in the screenshot as above. Unfortunately, still not getting the correct results, thanks!
@Anonymous , create a dense Rank on the date inside the country and try a formula like this
Like
Last 7 Dates = CALCULATE(SUM(Sales[Net Sales]),FILTER(allselected('Table'),'Table'[Rank]>=min('Table'[Rank])-7
&& 'Table'[Rank]<=max('Table'[Rank])))
or
Last 7 Dates = CALCULATE(SUM(Sales[Net Sales]),FILTER(allselected('Table'),'Table'[Rank]>=min('Table'[Rank])-7
&& 'Table'[Rank]<=max('Table'[Rank]) && && 'Table'[Country]=max('Table'[Country]) ))
Hi @amitchandak,
To create a rank on the date inside the country, how should I do it?
Do i create a conditional column for the dates and size when country = "Libya", then index that column?
Thanks!
@Anonymous , for Rank refer. use category/sub category rank
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |