Dax function R12M

[Axelle]

Hi,
I have written a measure in DAX to calculate the turnover of the past 12 months (Rolling 12 months) per customer:

CALCULATE([Turnover];
FILTER(ALL(T_Calendar);
T_Calendar[MonthNumber]=MONTH(TODAY())-1&&T_Calendar[Year]=YEAR(TODAY())
||T_Calendar[MonthNumber]=MONTH(TODAY())-2&&T_Calendar[Year]=Year(TODAY())
||T_Calendar[MonthNumber]=MONTH(TODAY())-3&&T_Calendar[Year]=Year(TODAY())
||T_Calendar[MonthNumber]=MONTH(TODAY())-4&&T_Calendar[Year]=Year(TODAY())
||T_Calendar[MonthNumber]=MONTH(TODAY())&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+1&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+2&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+3&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+4&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+5&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+6&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+7&&T_Calendar[Year]=Year(TODAY())-1
||T_Calendar[MonthNumber]=MONTH(TODAY())+8&&T_Calendar[Year]=Year(TODAY())-1))

Is there a way to write this more efficiently? I have to adjust this monthly.
Thanks in advance!

 

[gazpage]

Have a look at this article:

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

The key code is:

<code class="dax plain">Sales12M := </code><code class="dax color2">CALCULATE</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">    </code><code class="dax plain">[Sales],</code>
<code class="dax spaces">    </code><code class="dax color2">DATESBETWEEN</code> <code class="dax parenthesis">(</code>
<code class="dax spaces">        </code><code class="dax color2">Calendar</code><code class="dax plain">[FullDate],</code>
<code class="dax spaces">        </code><code class="dax color2">NEXTDAY</code> <code class="dax parenthesis">(</code> <code class="dax color2">SAMEPERIODLASTYEAR</code> <code class="dax parenthesis">(</code> <code class="dax color2">LASTDATE</code> <code class="dax parenthesis">(</code> <code class="dax color2">Calendar</code><code class="dax plain">[FullDate] </code><code class="dax parenthesis">)</code> <code class="dax parenthesis">)</code> <code class="dax parenthesis">)</code><code class="dax plain">,</code>
<code class="dax spaces">        </code><code class="dax color2">LASTDATE</code> <code class="dax parenthesis">(</code> <code class="dax color2">Calendar</code><code class="dax plain">[FullDate] </code><code class="dax parenthesis">)</code>
<code class="dax spaces">    </code><code class="dax parenthesis">)</code>
<code class="dax parenthesis">)</code>

You could replace the LASTDATE ( Calendar[FullDate] ) with TODAY ( ).

In order to use time intelligence you need a contiguous daily list of dates. If you don't have one already then you could create one and connect it to your existing calendar table.

 

[Axelle]

Hi,

thank you for your answer.
The article was also very interesting.

For this I need a measure that does not change depending on which dates in the filters, because I want to use this measure to create a calculated column in order to categorize customers.

now I have written it like this:

=CALCULATE([Turnover];
DATESBETWEEN(T_Calendar[Date];
DATE(YEAR(TODAY())-1;MONTH(TODAY());1);
DATE(YEAR(TODAY());MONTH(TODAY())-1;30)))

is there a way to make the last day of the month dynamic? I have written 30 because this is about April data. But for May this must be 31 ..

Thanks in advance!

 

[gazpage]

Adopt quite understand what you are doin, but look up EOMONTH () , or just add 1 month and subtract 1.