cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
coder_andy Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 419 members 3,616 guests
Please welcome our newest community members: