topic Re: Dynamic date calculation i.e. rolling total for 12-24 months ago in DAX Commands and Tips

Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 29 Apr 2021 14:12:23 GMT

Hi all you helpful people!

So I am trying to recreate an excel for someone (trying to convert everyone to PBI!!) which shows the following columns:

Product name (easy)
last 12 months rolling (managed that)
*12 months BEFORE the last 12 months rolling (this is where i come unstuck!)
Last 3 months rolling (ok with that)
*Same 3 month period the year before (eeek help!)
Last month total (fine and dandy)
*Same month total last year (arghghghghghhg!)

Please could you help me wiht those in with a *? I am a relative noob to this so am still fumbling my way around DAX. I was hoping SAMEPERIODLASTYEAR might help, but i couldnt get that to work on a table that wasn't date based.

My tables are called:

'Sales' with the relevant field being [Value]
'Date table' [Date] or 'Sales' [relative continuous month]

Hoping one of you can help me 🙂

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 29 Apr 2021 14:46:08 GMT

Hi gingerclaire -

You said someting about having a table that is not date-based, but you also say you have a Date table. Is the Date table a Calendar table? DAX Time Intelligence functions like SAMEPERIODLASTYEAR require a date table that contains every date, not just months. With that type of table you can still join to Sales that only has one date per month.

See here for more about Calendar tables: CALENDAR ; CALENDARAUTO

If you don't have the ability to create tables, you may want to check out Greg_Deckler 's "To *bleep* with Time Intelligence"

Finally if you can create/manage tables and you're still having issues, please post your measure code and some sample data, or a link to a copy of your pbix file that has sensitive data removed.

Hope this helps

David

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 29 Apr 2021 14:48:38 GMT

Sorry - I do have a date table. I meant that I am trying to create this visual as a matrix or table which has products as the first column.

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 29 Apr 2021 15:17:19 GMT

Understood. I would still suggest looking at Greg Deckler's blog, as that may inspire you.

Other than that, we'd have to see your code, data and model to begin to help further.

David

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 11:57:23 GMT

Hi David - I don't have any code - that is the problem. At the moment I am using these measures to work out the last 12, 3 and 1 month and they seem to be working:

Sales in last 1 month = CALCULATE([Total sales], datesinperiod(Sales[Relative continuous month], MAX(Sales[Relative continuous month]),-1,MONTH))

The ones I am having trouble with are:

Last month 12 months ago (i.e. sum of 13 months)
Last 3 months 12 months ago (i.e. sum of 4-6 months ago)
Last 12 months 12 months ago (i.e. sum of 12-24 months ago)

I cant share the data as it is sensitive but if you let me know exactly what you would need to see i can screenshot that?

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

jdeveaux — Thu, 06 May 2021 12:04:33 GMT

Hello,

Do you have easily access to the current/selected date (or month) ?

If yes, you could use a little trick with selected value and a Calculate(yourMeasure;ALL(Date);SelectedDate - 12 month).

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 12:10:31 GMT

Sorry - but i dont understand what you mean by 'Do you have easily access to the current/selected date (or month) ?' How would i do this/set this?

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

jdeveaux — Thu, 06 May 2021 12:14:01 GMT

What kind of visual are you trying to set up? Is there any date axis?

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 12:19:19 GMT

It is just a table/matrix, so no date axis as such.

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 12:59:46 GMT

So I have created a measure called CM Max Date to return my most recent date.

CM Max date = MAX(Sales[Relative continuous month])

Then i created this:

CM year last year = Calculate([CM Sales in last 12 months],ALL(Sales),[CM Max date],-12,month)

But i get the following error

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 06 May 2021 13:30:15 GMT

Hi gingerclaire -

What we need to do is build a flexible FILTER statement to get only the dates we want, then apply that filter to the measure. Have a try with this code (modify as necessary)

MyMeasureFiltered = VAR __StartOffset = 12 //How many months back from current month? VAR __EndOffset = 0 //use 0 for current month, otherwise how many months back from current month VAR __StartDt = //goes back to the end of the month X+1 months ago, //then goes to the 1st of the following month EOMONTH ( TODAY (), -1 * ( __StartOffset + 1 ) ) + 1 VAR __EndDt = EOMONTH ( TODAY (), -1 * __EndOffset ) RETURN CALCULATE ( [MyMeasure], FILTER ( ALL ( DateTab ), DateTab[Date] >= __StartDt && DateTab[Date] <= __EndDt ) )

What I've shown is your 13 months filter (this month + 12 behind). Change the offsets to get to your other use cases. What you use for the offsets depends on your definition of "X Months Ago". Is 3 months ago February, or is it March?

Hope this helps

David

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 13:55:09 GMT

Thank you! What would [MyMeasure] be in this case? My 1 month measure? Or my latest date measure?

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 14:10:32 GMT

I have tried this using the following, but it seems to add up all sales that have happened in the last 13 months.

I am trying to show the total for month -13 only (and also then -13-15 and -13 - 24)

CM 12 months plus 1 month =
VAR __StartOffset = 12
VAR __EndOffset = 0
VAR __StartDt =
EOMONTH ( TODAY (), -1 * ( __StartOffset + 1 ) ) + 1
VAR __EndDt =
EOMONTH ( TODAY (), -1 * __EndOffset )
RETURN
CALCULATE (
[Total sales],
FILTER (
ALL ( Sales[Relative continuous month] ),
Sales[Relative continuous month] >= __StartDt
&& Sales[Relative continuous month] <= __EndDt
)
)

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 06 May 2021 14:10:50 GMT

For just one month, set StartOffset and EndOffset to be the same.

And [MyMeasure] should be whatever you are using for one month. Can you share the code for that measure?

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 14:14:40 GMT

CM Sales in last 1 month = CALCULATE([Total sales], datesinperiod(Sales[Relative continuous month], MAX(Sales[Relative continuous month]),-1,MONTH))

If i use that in the dax it returns the same as my one month sales

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 06 May 2021 14:15:09 GMT

[Total Sales] will be [MyMeasure]

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 14:18:16 GMT

Thank you so much for your patience with me. When i use total sales as mymeasure, it seems to show the total for the last 13 months together (rather than just the total for month 13)?

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 06 May 2021 14:23:21 GMT

Did you set StartOffset and EndOffset to be the same?

MyMeasureFiltered = VAR __StartOffset = 3 VAR __EndOffset = 3 VAR __StartDt = EOMONTH ( TODAY (), -1 * ( __StartOffset + 1 ) ) + 1 ...

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

gingerclaire — Thu, 06 May 2021 14:34:07 GMT

Yes - i used this code:

Re: Dynamic date calculation i.e. rolling total for 12-24 months ago

dedelman_clng — Thu, 06 May 2021 14:39:00 GMT

You have to change the values of __StartOffset and __EndOffset in the code. You still have them as 12 and 0. For one month's worth they have to be the same. You will need to create a different measure for every scenario of __StartOffset and __EndOffset.

Also, you said that you had a calendar/date table. You are applying this to your sales table which may not work as you want it to.