Forum Discussion
Delta Between Two Dates Aggregation
In Power BI I have two tables.
Holidays:
Table_1 (My Main Table):
In Table_1 I created a measure. This measure calculates the difference between two dates and excludes weekends and holidays.
Delta = CALCULATE(SUM(Holidays[Weekend/Holiday]), FILTER(Holidays, Holidays[Date] >= FIRSTDATE('Table_1'[Opened]) && Holidays[Date] < LASTDATE('Table_1'[Closed])))
The issue is, this measure works when each row is individually displayed like above. However when I remove the dates and aggregate, I get a delta of 353. Not sure where this comes from, but I would like the average or median delta calculation. Any ideas?
Hi Anonymous ,
Would you please try the measure below:
measure = AVERAGEX ( SUMMARIZE ( 'Table_1', 'Table_1'[Opened], 'Table_1'[Closed], "_delta", [Delta] ), [_delta] ) measure2 = MEDIANX ( SUMMARIZE ( 'Table_1', 'Table_1'[Opened], 'Table_1'[Closed], "_delta", [Delta] ), [_delta] )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
1 Reply
Hi Anonymous ,
Would you please try the measure below:
measure = AVERAGEX ( SUMMARIZE ( 'Table_1', 'Table_1'[Opened], 'Table_1'[Closed], "_delta", [Delta] ), [_delta] ) measure2 = MEDIANX ( SUMMARIZE ( 'Table_1', 'Table_1'[Opened], 'Table_1'[Closed], "_delta", [Delta] ), [_delta] )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai