cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WhiteLight_FI Regular Visitor
Regular Visitor

SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

Hello everyone,

 

I searched the forum and I didn't find any cases similar to mine. 

 

I would like to create a "SUM CONDITIONAL". Indeed, I would like to make the sum of the sales by taking into account two different time parameters and for that I had to rotate my two date columns. Please notice that the two dates are linked by an unique ID number.

 

The first type of date corresponds to the recorded sale in YYYY/MM/DD and the second type corresponds to the effective date of the sale in YYYY/MM/DD format.

 

I would like to make a formula that gives me in a table the number of sales recorded in August 2019 and that will take effect over the next few years.

 

Could you please help me ? 

 

Thank you very much.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

A lot of this depends on how your data model is are set up, since the relationships here are important.  It's also important that you know how you want to display/use this particular measure.

 

Making a best guess, it seems like you would just use your dates as two filter conditions, just like any other measure that has multiple fields to filter by.  This is no different from using [Size] = "Large" && [Color] = "Red" as filter conditions.


This returns a sum of [Value] where the Recorded date is in the month of August 2019, and the Effective date is between August 1 2019, and August 1 2022. You can adjust the DATESINPERIOD parameters as you see fit for your case

SumBasedOnTwoDates =
CALCULATE (
    SUM ( Table1[Value] ),
    DATESINPERIOD ( Table1[RecordedDate], DATE ( 2019, 8, 1 ), 1, MONTH ),
    DATESINPERIOD ( Table1[EffectiveDate], DATE ( 2019, 8, 1 ), 3, YEAR )
)

View solution in original post

Super User
Super User

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

It sounds like you did your unpivoting in a way that made your data much harder to work with.  If every record has a value for each of these dates, why unpivot them in the first place?  

 

If you toally, absolutely, 100%, for sure had to unpivot this data (I would strongly recommend re-evaluating that decision), then you can use CALCULATE to change the context of the DATESINPERIOD functions. 

 

This starts wildly complicating your query plan, and you lose a LOT of performance due to the constant context switching that needs to be done.  In fact, I'm not sure it can be done without re-pivoting the data in the measure, since DAX really doesn't like switching between different rows.  It may be possible if you have a second related table that you group by, but the query gets really messy, and I don't want to start guessing at possible data models you may be using.

 

 

 

View solution in original post

5 REPLIES 5
WhiteLight_FI Regular Visitor
Regular Visitor

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

One more thing, Please notice that the concerned date are linked to a simple calendar table. 

 

Thank you

Super User
Super User

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

A lot of this depends on how your data model is are set up, since the relationships here are important.  It's also important that you know how you want to display/use this particular measure.

 

Making a best guess, it seems like you would just use your dates as two filter conditions, just like any other measure that has multiple fields to filter by.  This is no different from using [Size] = "Large" && [Color] = "Red" as filter conditions.


This returns a sum of [Value] where the Recorded date is in the month of August 2019, and the Effective date is between August 1 2019, and August 1 2022. You can adjust the DATESINPERIOD parameters as you see fit for your case

SumBasedOnTwoDates =
CALCULATE (
    SUM ( Table1[Value] ),
    DATESINPERIOD ( Table1[RecordedDate], DATE ( 2019, 8, 1 ), 1, MONTH ),
    DATESINPERIOD ( Table1[EffectiveDate], DATE ( 2019, 8, 1 ), 3, YEAR )
)

View solution in original post

WhiteLight_FI Regular Visitor
Regular Visitor

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

Hello  Smiley Happy ;

 

Thank you so much for your help !

 

When I try to perform your formula I quickly find myself confronted to a problem. Because in the DATEINPERIOD I can only call up the title of a column and not its content.

 

I explain myself, I had at the beginning two columns of different dates and in order to link them to the calendar table, I had to make a "unpivot columns" to have in a column the title of the dates and in a second column the dates.

 

Do you know any way to avoid this or any other formula that would suit my needs?

 

Thanks you very much again.

 

 

Super User
Super User

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

It sounds like you did your unpivoting in a way that made your data much harder to work with.  If every record has a value for each of these dates, why unpivot them in the first place?  

 

If you toally, absolutely, 100%, for sure had to unpivot this data (I would strongly recommend re-evaluating that decision), then you can use CALCULATE to change the context of the DATESINPERIOD functions. 

 

This starts wildly complicating your query plan, and you lose a LOT of performance due to the constant context switching that needs to be done.  In fact, I'm not sure it can be done without re-pivoting the data in the measure, since DAX really doesn't like switching between different rows.  It may be possible if you have a second related table that you group by, but the query gets really messy, and I don't want to start guessing at possible data models you may be using.

 

 

 

View solution in original post

Community Support Team
Community Support Team

Re: SUM CONDITIONAL WITH 2 DIFFERENT TYPE DATE

Hi @WhiteLight_FI 

 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!

 

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)