Forum Discussion
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 caseSumBasedOnTwoDates = CALCULATE ( SUM ( Table1[Value] ), DATESINPERIOD ( Table1[RecordedDate], DATE ( 2019, 8, 1 ), 1, MONTH ), DATESINPERIOD ( Table1[EffectiveDate], DATE ( 2019, 8, 1 ), 3, YEAR ) )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.
Resident RockstarA 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 )
)Hello Cmcmahan :smileyhappy: ;
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.
- Cmcmahan
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.
- v-diye-msft
Community Support
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!
