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 there!
I did some searches over the PBI Community but I didn't anything how can I calculate moving average/sum with certain months with no data.
I need something like as below:
Date | Sum of events Mo | Sum of events MovAvg 6 Mo |
01-03-2020 | 1 | 1 |
01-04-2020 | zero events (no data) | 1 |
01-05-2020 | 2 | 3 |
01-06-2020 | zero events (no data) | 3 |
01-07-2020 | 1 | 4 |
01-08-2020 | 6 | 10 |
01-09-2020 | 2 | 11 |
Note: the blue line above just show you an example that I need for the next rows.
I am using this DAX:
Solved! Go to Solution.
Hello @Anonymous
Is this what you are looking for?
If yes, then you may use the following measure:
Runnning Sum =
VAR _CurrentDate = MAX(ftCalendar[Date])
VAR _StartDate = EOMONTH(_CurrentDate,-7)+1
VAR _Filter =
FILTER(
All(ftCalendar[Date]),
ftCalendar[Date] >= _StartDate
&& ftCalendar[Date] <=_CurrentDate
)
VAR _Sum =
CALCULATE(SUM('Table'[Sum of events Mo]),_Filter)
RETURN
_Sum
If not, then please share more details in terms of expected output.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Hello @Anonymous
According to the sample table you gave,I think that the [Sum of events MovAvg 6 Mo] value of the blue line should be 12,you should check if you enter the wrong value. if it's 12,you can try the following measure:
Events 6 MO =
CALCULATE
(SUM('Table'[Sum of events Mo])
,FILTER(ALLSELECTED('Table')
,'Table'[Date]<=MAX('Table'[Date])))
and you can get the output chart like this:
If not,please share more details to get your expected output.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Robert Qin
Hello @Anonymous
According to the sample table you gave,I think that the [Sum of events MovAvg 6 Mo] value of the blue line should be 12,you should check if you enter the wrong value. if it's 12,you can try the following measure:
Events 6 MO =
CALCULATE
(SUM('Table'[Sum of events Mo])
,FILTER(ALLSELECTED('Table')
,'Table'[Date]<=MAX('Table'[Date])))
and you can get the output chart like this:
If not,please share more details to get your expected output.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Robert Qin
Hi,
Try this measure
=calculate([Sum of Events (All)],datesbetween(dCalendario[Data],edate(min(dCalendario[Data]),-5),max(dCalendario[Data])))
Hope this helps.
Hello @Anonymous
Is this what you are looking for?
If yes, then you may use the following measure:
Runnning Sum =
VAR _CurrentDate = MAX(ftCalendar[Date])
VAR _StartDate = EOMONTH(_CurrentDate,-7)+1
VAR _Filter =
FILTER(
All(ftCalendar[Date]),
ftCalendar[Date] >= _StartDate
&& ftCalendar[Date] <=_CurrentDate
)
VAR _Sum =
CALCULATE(SUM('Table'[Sum of events Mo]),_Filter)
RETURN
_Sum
If not, then please share more details in terms of expected output.
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |