DAX How to calculate SUMX of an expression with dynamic filter from report filters

[coder_andy]

I am struggling with writing a DAX for my measure.

Consider following simplified data model:

• Customer able with 2 fields: CustomerId, CustomerName
• Stay table with 5 fields: StayId, CustomerId, StayStartDate, StayEndDate, Cost

I have a report filter for the reporting period (from and start dates).

Now based on the selected report filter dates I need to calculate the total Days of Stay within the reporting period.

example., If reporting period is 1/1/2019, to 1/10/2019, I need to calculate a measure as a sum of all Days of Stay that overlap this period. These could be:

• Case 1: Stays that started before 1/1/2019, end after 1/10/2019 => In this case count will be10 days
• Case 2a: Stays that started before 1/1/2019, end before 1/10/2019 => in this case count of days is the number of days from 1/1/2019 to the actual StayEndDate (<10 days)
• Case 2b: Stays that started after 1/1/2019, end after 1/10/2019 => in this case count of days is the number of stay days from StartDate to 1/10/2019 (<10 days)
• Case 3: Stays that started after 1/1/2019, end before 1/10/2019 => This will be the acual number days stayed (StayEndDate - StayStartDate)

I thought it will be easier to split the DAX into above 4 cases and then take a sum of all. Following is what I have written so far for Case 1:

CALCULATE ( 
    SUMX( Stay, 
        DATEDIFF(ReportStartPeriod, ReportEndPeriod, DAY)
    ),
    ALL(Stay),
    FILTER( Stay, Stay[StayStartDate] <= ReportStartPeriod
        && (Stay[StayEndDate] > ReportEndPeriod
    ) 
)        

The above works but does not respect any other filters on Stay, e.g. Stay Days by Customer doesn't work. I tried replacing ALL with ALLSELECTED and ALLXCEPT but then I'm not sure how to write the additional Filter condition.

Any help will be appreciated.

[Anonymous · Accepted solution]

Hi @coder_andy ,

In fact, current power bi does not support filter on multiple columns.

You can take a look at the following link about create a bridge table to store all expand the date range records for calculate:

Spread revenue across period based on start and end date, slice and dase this using different dates

Regards,
Xiaoxin Sheng