cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cfpvin Helper III
Helper III

Filter three values according to three different dates

Hello,

 

Is it possible to get sum of three measures where the three measures are affected by three different date ranges?

I need Data = Measure1 + Measure2 + Measure3

Measure1 is affected by date range A, Measure2 is affected by date range B and Measure3 is affected by date range C.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Filter three values according to three different dates

Right, sample data explains everything. So what you want is:

 

I need Data = 
CALCULATE([Measure1],ALLEXCEPT('Table'[Date1])) + 
  CALCULATE([Measure2],ALLEXCEPT('Table'[Date2])) + 
    CALCULATE([Measure3],ALLEXCEPT('Table'[Date3]))

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: Filter three values according to three different dates

Really, really hard to tell. Would need to see the formulas of the measures. Sample data would b great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Filter three values according to three different dates

@cfpvin not sure how your data model looks like, if you already correctly calculated your base 3 measure then new measure can be just sum of these three, something like this

 

New measure = [Measure 1] + [Measure 2] + [Measure 3]

 

Would appreciate Kudos 🙂 if my solution helped. 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





cfpvin Helper III
Helper III

Re: Filter three values according to three different dates

Sum CompletedDate 1Date2Date3
101/02/2020  
202/02/2020  
303/02/202005/02/2020 
404/02/202006/02/2020 
505/02/202007/02/2020 
606/02/202008/02/2020 
707/02/202009/02/2020 
808/02/202010/02/202010/02/2020
909/02/202011/02/202011/02/2020
1010/02/202012/02/202012/02/2020
1111/02/202013/02/202013/02/2020
1212/02/202014/02/202014/02/2020
1313/02/202015/02/202015/02/2020
1414/02/202016/02/202016/02/2020

 

Measure1, measure2 and measure3 are going to be sum of sum completed. If we adjust date slicer date1 from 01/02/2020 to 04/02/2020, date2 from 05/02/2020 to 09/02/2020 and date3 from 10/02/2020 to 16/02/2020 for measure1, measure2 and measure3 respectively, I want the sum of the measures to be for measure1 with date1 range, measure2 with date2 range and measure3 with date3 range.

 

cfpvin Helper III
Helper III

Re: Filter three values according to three different dates

@Greg_Deckler 

Sum CompletedDate 1Date2Date3
101/02/2020  
202/02/2020  
303/02/202005/02/2020 
404/02/202006/02/2020 
505/02/202007/02/2020 
606/02/202008/02/2020 
707/02/202009/02/2020 
808/02/202010/02/202010/02/2020
909/02/202011/02/202011/02/2020
1010/02/202012/02/202012/02/2020
1111/02/202013/02/202013/02/2020
1212/02/202014/02/202014/02/2020
1313/02/202015/02/202015/02/2020
1414/02/202016/02/202016/02/2020

 

Measure1, measure2 and measure3 are going to be sum of sum completed. If we adjust date slicer date1 from 01/02/2020 to 04/02/2020, date2 from 05/02/2020 to 09/02/2020 and date3 from 10/02/2020 to 16/02/2020 for measure1, measure2 and measure3 respectively, I want the sum of the measures to be for measure1 with date1 range, measure2 with date2 range and measure3 with date3 range.

 

Super User IV
Super User IV

Re: Filter three values according to three different dates

Right, sample data explains everything. So what you want is:

 

I need Data = 
CALCULATE([Measure1],ALLEXCEPT('Table'[Date1])) + 
  CALCULATE([Measure2],ALLEXCEPT('Table'[Date2])) + 
    CALCULATE([Measure3],ALLEXCEPT('Table'[Date3]))

 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors