cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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
Super User

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]))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

@Anonymous 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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable
 Sum Completed Date 1 Date2 Date3 1 01/02/2020 2 02/02/2020 3 03/02/2020 05/02/2020 4 04/02/2020 06/02/2020 5 05/02/2020 07/02/2020 6 06/02/2020 08/02/2020 7 07/02/2020 09/02/2020 8 08/02/2020 10/02/2020 10/02/2020 9 09/02/2020 11/02/2020 11/02/2020 10 10/02/2020 12/02/2020 12/02/2020 11 11/02/2020 13/02/2020 13/02/2020 12 12/02/2020 14/02/2020 14/02/2020 13 13/02/2020 15/02/2020 15/02/2020 14 14/02/2020 16/02/2020 16/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

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable
 Sum Completed Date 1 Date2 Date3 1 01/02/2020 2 02/02/2020 3 03/02/2020 05/02/2020 4 04/02/2020 06/02/2020 5 05/02/2020 07/02/2020 6 06/02/2020 08/02/2020 7 07/02/2020 09/02/2020 8 08/02/2020 10/02/2020 10/02/2020 9 09/02/2020 11/02/2020 11/02/2020 10 10/02/2020 12/02/2020 12/02/2020 11 11/02/2020 13/02/2020 13/02/2020 12 12/02/2020 14/02/2020 14/02/2020 13 13/02/2020 15/02/2020 15/02/2020 14 14/02/2020 16/02/2020 16/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

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]))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors