Delta Between Two Dates Aggregation

[Anonymous]

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?

[v-deddai1-msft · Accepted solution]

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