Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi, I got a date table [DateTable] and a table of sales [Sales]. I've successfullt created a rolling 12m for sales:
RoSales 12m = VAR minDate = DATE ( YEAR ( MAX ( DateTable[Date] ) ); MONTH ( MAX ( DateTable[Date] ) ) - 12; DAY ( MAX ( DateTable[Date] ) ) ) RETURN CALCULATE ( SUM ( Sales[Sales] ); FILTER ( DateTable; DateTable[Date] > minDate && DateTable[Date] <= MAX ( DateTable[Date] ) ) )
What I want to do, and failed repeatedly to, is to get a chart with dates on the X-axis and the Sales for the last 12 months at each date on the Y-axis.
Example if in table:
|____Date_______|_Rolling 12__| | 10 Aug 2017 | 100K | | 11 Aug 2017 | 101K | | 12 Aug 2017 | 108K | | 13 Aug 2017 | 107K |
All my attempts results in the measure taking in the context of the date and plots out the sum for that date, when I really want the sum of the whole last year with the date as end point. Is this possible?
Cheers!
Solved! Go to Solution.
Why do you have that additional reference to the Date extension? The formula should look like this:
test 12m = CALCULATE ( SUM ( Sales[SalesAmount] ); DATESINPERIOD ( DateTable[Date]; CALCULATE ( MAX ( DateTable[Date] ) ); -1; YEAR ) )
Notice the missing .[Date] in the first parameter of DATESINPERIOD (DateTable[Date]).
The extension created by Power BI contains the full year. My advice is always the same with auto date/time: disable it, learn time intelligence and forget about its existence 🙂
Anyway, removing that reference should fix the problem although a date table ending in August 26 is not a best practice, the best would be to protect your code using an IF statement that blanks the measure. With that said, in your special case, you can live with an incomplete date table.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
Alberto
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |