Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All -
I can't quite figure out why my formula isn't working here. I'm trying to calculate the sales from the past 30 months using the following:
TotalSales30Day = CALCULATE ( SUM ( Orders[Quantity] ), DATESINPERIOD ( CalendarTable[Date], MAX ( CalendarTable[Date] ), -30, DAY ) )
but the result is blank. My CalendarTable is related to the sales table date.
The Dates columns are formatted as Date (not Date/Time). Quantity is a whole number.
Here's a snapshot of the Orders table (and yes, sales go back much further than today 😞
Thoughts?
Thanks
Travis
Solved! Go to Solution.
Hi @a68tbird
It seems you may try to use below measure:
TotalSales30Day = CALCULATE ( SUM ( Orders[Quantity] ), DATESINPERIOD ( Orders[OrderDate], MAX ( Orders[OrderDate] ), -30, DAY ) )
Regards,
Cherie
Hi - i think you want...
TotalSales30Day = CALCULATE ( SUM ( Orders[Quantity] ), DATESINPERIOD ( CalendarTable[Date], LASTDATE ( CalendarTable[Date] ), -30, DAY ) )
Thanks johnmc, but that didn't seem to do anything for me either.
Hi @a68tbird
It seems you may try to use below measure:
TotalSales30Day = CALCULATE ( SUM ( Orders[Quantity] ), DATESINPERIOD ( Orders[OrderDate], MAX ( Orders[OrderDate] ), -30, DAY ) )
Regards,
Cherie
Thanks Cherie. That did work. I guess there's something wrong with my relationship between the calendar table and orders table? I can't understand why my original formula didn't work. All other examples of this formula that I've seen, use reference to the calandar table.
But all good!
User | Count |
---|---|
94 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |