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

[gingerclaire]

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 🙂

[dedelman_clng · Accepted solution]

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

[dedelman_clng]

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

[gingerclaire]

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.

[dedelman_clng]

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

[gingerclaire]

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?

[dedelman_clng · Accepted solution]

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

[gingerclaire]

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
)
)

[dedelman_clng]

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?

[gingerclaire]

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

[dedelman_clng]

[Total Sales] will be [MyMeasure]

[gingerclaire]

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)?

[dedelman_clng]

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
...

[gingerclaire]

Yes - i used this code:

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
)
)

[dedelman_clng · Accepted solution]

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.   

[gingerclaire]

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

[jdeveaux]

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).

[gingerclaire]

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?

[jdeveaux]

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

[gingerclaire]

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

[gingerclaire]

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

[gingerclaire]

With so much thanks to dedelman_clng (a lot of help and patience from him!!).

I have delayed sales data (often by two months) so i created a measure (CM last month) which returns the most recent month i have sales data for and modified the formula so that it only worked back from that date.

Here is the formula (example given is for this month last year):

CM 12 months plus 1 month =
VAR __StartOffset = 12
VAR __EndOffset = 12
VAR __StartDt =
EOMONTH ( [CM last month] (), -1 * ( __StartOffset + 1 ) ) + 1
VAR __EndDt =
EOMONTH ( [CM last month] (), -1 * __EndOffset )
RETURN
CALCULATE (
[Total sales],
FILTER (
ALL ( 'DATE TABLE' ),
'DATE TABLE'[Date] >= __StartDt
&& 'DATE TABLE'[Date] <= __EndDt
)
)

The offsets were a little confusing as they start from 0 so i made an idiots guide to help me! i.e i woul dhave assumed that the last three months would be 1/3 but it is actually 0/2