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.
Hi all,
I ran into a problem and will really appreicate any help.
I have this dataset (Cases) with two columns, Date and Cases.
Date | Cases |
1/1/2020 | 65 |
1/2/2020 | 86 |
1/3/2020 | 35 |
1/4/2020 | 85 |
1/5/2020 | 42 |
1/6/2020 | 68 |
1/7/2020 | 98 |
1/8/2020 | 86 |
1/9/2020 | 114 |
1/10/2020 | 64 |
1/11/2020 | 95 |
1/12/2020 | 92 |
1/13/2020 | 57 |
I was able to calculate the moving average of cases (Moving average) and day by day % change of the moving average (Moving %ch) using below DAX functions. Both results are measures.
Solved! Go to Solution.
Hi,@Sam10
According to your description, there is an error in your measure while using the time intelligence.
For time intelligence functions, we recommend that you use it with the Date table. It may solve the problem that always gives 0 and blanks.
You can do as the following steps:
1.Create a date table and create relationship between Date table and Cases table.
Date table = CALENDARAUTO()
2.Create a new measure for average in 7 days.
Average in 7 day =
var _sum=CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date table'[Date],MAX('Date table'[Date]),-7,Day))
Return
_sum/7
3.It's a nice reply of @amitchandak,you can follow it:
Average % =
VAR _Rolling7day =
CALCULATE (
SUM ( 'Cases'[Cases] ),
DATESINPERIOD ( 'Date table'[Date], MAX ( 'Date table'[Date] ), -7, DAY )
) / 7
VAR _Rolling7daylastDay =
CALCULATE (
SUM ( 'Cases'[Cases] ),
DATESINPERIOD ( 'Date table'[Date], MAX ( 'Date table'[Date] ) - 1, -7, DAY )
) / 7
RETURN
DIVIDE ( _Rolling7day - _Rolling7daylastDay, _Rolling7daylastDay )
Then you can meet your needs like this:
Here is the demo,please try it:
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Works great! Thank you for helping.
Hi,@Sam10
According to your description, there is an error in your measure while using the time intelligence.
For time intelligence functions, we recommend that you use it with the Date table. It may solve the problem that always gives 0 and blanks.
You can do as the following steps:
1.Create a date table and create relationship between Date table and Cases table.
Date table = CALENDARAUTO()
2.Create a new measure for average in 7 days.
Average in 7 day =
var _sum=CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date table'[Date],MAX('Date table'[Date]),-7,Day))
Return
_sum/7
3.It's a nice reply of @amitchandak,you can follow it:
Average % =
VAR _Rolling7day =
CALCULATE (
SUM ( 'Cases'[Cases] ),
DATESINPERIOD ( 'Date table'[Date], MAX ( 'Date table'[Date] ), -7, DAY )
) / 7
VAR _Rolling7daylastDay =
CALCULATE (
SUM ( 'Cases'[Cases] ),
DATESINPERIOD ( 'Date table'[Date], MAX ( 'Date table'[Date] ) - 1, -7, DAY )
) / 7
RETURN
DIVIDE ( _Rolling7day - _Rolling7daylastDay, _Rolling7daylastDay )
Then you can meet your needs like this:
Here is the demo,please try it:
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@Sam10 , Not very clear, but it should be last 7 and last 7 for yesterday like given example
Rolling 7 day = CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-7,Day)) /7
Rolling 7 day last Day = CALCULATE(SUM('Cases'[Cases]),DATESINPERIOD('Date'[Date],MAX('Date'[Date])-1,-7,Day))/7
Change % = divide( [Rolling 7 day] -[Rolling 7 day last Day],[Rolling 7 day last Day])
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thank you for the response. Now from here, how we calculate the average of the Change % as a column or a measure? I was not be able to do that. Below DAX functions can run without errors, but only give me 0 and blanks.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |