Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 ) )
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.
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 ) )
Hello Cmcmahan ;
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!
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.
One more thing, Please notice that the concerned date are linked to a simple calendar table.
Thank you
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |