Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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

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
Cmcmahan
Resident Rockstar
Resident Rockstar

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 )
)
Anonymous
Not applicable

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.

 

 

Hi @Anonymous 

 

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.

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.

 

 

 

Anonymous
Not applicable

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

 

Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.