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, I am creating a moving average measure to be ploted against time with the following formula:
6 Month Moving Average = CALCULATE(
AVERAGE('public data'[data to calculate]);
DATESINPERIOD('public data'[data related date];
LASTDATE(Dates[Date]);
-6;
MONTH)
)
Here Dates contain the Date column with date values from 2017-01-01 to 2018-12-31. When I plot this measure against Dates[Date], I get what I expect, but only up to 16th of June, however there are data points up 15th of June, so it should evaluate the result up to 16th of December, when the last data point falls out of the 6 months window.
If I change the expression like:
Test1 = CALCULATE( AVERAGE('public data'[data to calculate]); DATESINPERIOD('public data'[data related date]; DATEVALUE("2017-06-16"); -6; MONTH) ) Test2 = CALCULATE( AVERAGE('public data'[data to calculate]); DATESINPERIOD('public data'[data related date]; DATEVALUE("2017-06-17"); -6; MONTH) )
then Test1 evaluates to the correct value, but Test2 is (Blank). Have anybody encountered such an error? I suspect that there is something I misunderstand with DATESINPERIOD function. Thanks for the help!
Hi @Anonymous,
This behaviour might be related to actual data records. Based on my test, the maximum available value for <start_date> part could be the maximum date in your source table 'public data'[data related date]. Otherwise, the measure will return blank value.
DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)
By the way, using Test1, have you got desired output? If not, please share some sample data and show us your expected result with an image so that I can test for you.
Best regards,
Yuliana Gu
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |