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 use one large data file in which all incidents are reported. Both the incident and the impact of the incident is reported in this source file. To calculate the moving average over several months, I use the SUMMARIZE function to create a new table. In this table I either count the IDs summarized by YearMonth column, or I SUM the impact of the incidents summarized by the YearMonth column. Then I proceed calculating the moving average.
The issue I'm having is the following: When a month has 0 incidents, it is not recorded as a value. I would like to summarize all the months and print a 0-value for months in which no incidents occured (with the specific filter).
Here is the formula I use to summarize the data:
M2.1 = SUMMARIZE(CALCULATETABLE(Incidents;Incidents[Category]="D3";Cause[Scope]=1;'Impact level'[Impact level]<=4);Incidents[YearMonth];"Summed impact per incident";SUM(Incidents[Impact]))
The Date column:
Date = DATE(LEFT('M2.1'[YearMonth];4);RIGHT('M2.1'[YearMonth];2);1)
And to calculate moving average:
Moving average =
var startdate=Min('M2.1'[Date])+334
var date = 'M2.1'[Date]
var d365 = EDATE(date;-12)
var r=
IF(date>=startdate;
AVERAGEX( FILTER('M2.1'; 'M2.1'[Date] <= date && 'M2.1'[Date] > d365);
'M2.1'[Summed impact per incident] )
)
RETURN
r
Any help is appreciated!
Solved! Go to Solution.
Hi,
I recently figured out how to solve the problem I was having. If I break it down, the real problem was that I wanted the table I create (as a summary of all the data in the master data file) did not show the months in which no data was recorded. So what I did is the following:
I created a static table with all the YearMonth combinations for the forseeable future and recorded past. (i.e. 2013-01 untill 2050-12)
Then I created a new calulated table with the DAX: Distinct(YearMonth) to print every individual value into this table.
Following I created an IF calculation on the SUM (and separately COUNT) of the incidentents in the following way:
IF(CALCULATE(SUM(<incidents>);<filters>)=blank();0;
CALCULATE(SUM(<incidents>);<filters>))
It works perfectly in this situation. Because this situation called for a moving average calculation over several fillers on data, I couldn't find a way to use the measure functionality. I would love to see the moving average be a feature in the visualization level in some future release. My opinion on the matter is that this should be Chart-functionality, not something I need to calculate myself.
Hi @Philip-K,
Please elaborate your data model structure and provide us some sample data, so that we can make further analysis.
Regards,
Charlie Liao
Hi,
I recently figured out how to solve the problem I was having. If I break it down, the real problem was that I wanted the table I create (as a summary of all the data in the master data file) did not show the months in which no data was recorded. So what I did is the following:
I created a static table with all the YearMonth combinations for the forseeable future and recorded past. (i.e. 2013-01 untill 2050-12)
Then I created a new calulated table with the DAX: Distinct(YearMonth) to print every individual value into this table.
Following I created an IF calculation on the SUM (and separately COUNT) of the incidentents in the following way:
IF(CALCULATE(SUM(<incidents>);<filters>)=blank();0;
CALCULATE(SUM(<incidents>);<filters>))
It works perfectly in this situation. Because this situation called for a moving average calculation over several fillers on data, I couldn't find a way to use the measure functionality. I would love to see the moving average be a feature in the visualization level in some future release. My opinion on the matter is that this should be Chart-functionality, not something I need to calculate myself.
Hi @Philip-K
You can make it shorter, I think:
CALCULATE(SUM(<incidents>);<filters>)+0
Great addition, thanks!
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 | |
95 | |
77 | |
68 | |
54 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |