Forum Discussion

PowerPirate's avatar
PowerPirate
New Member
6 years ago
Solved

Trailing 12-Month Units in Calculated Column - Lower Bound Not Working

Hi All,

 

I have a table I created called Red Products using CALCULATETABLE and SUMMARIZE referencing my Total Sales by Year table, which has the raw data I've queried in. In my calculated table, I have several columns including the transaction date and customer number. I created a calculated column using the following code to calculate the trailing 12 months of units of a specifc product:

 

Trailing 12 Volume = CALCULATE(
[Red Units],
DATESBETWEEN(
'Red Products'[Date],
NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Red Products'[Date]))),
LASTDATE('Red Products'[Date])
)
)
 
I have spot checked several customer numbers manually and I'm finding if I use the code as above, it's only returning the number of units for the month of the transaction date in that row of the table. I have a master date table, and if I change the code to the below, it now returns every unit starting in the transaction date month going all the way back to to that start of my raw data in 2013:
 
Trailing 12 Volume = CALCULATE(
[Primary Femur Units],
DATESBETWEEN(
'Total Sales by Year'[Date],
NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Total Sales by Year'[Date]))),
LASTDATE('Total Sales by Year'[Date])
)
)
 
What am I not understanding here where I can't seem to get the lower boundary to stick right? I've been going at this for over 6 hours and am totally stumped. I've found so many examples/forum posts of folks using the same trailing 12 formula successfully. Would be really grateful for any help here. Happy to post any additional information that would be helpful. I'm slowing working my way through the definitive guide to dax so I'm still very new to coding.
 
Thanks!
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi PowerPirate,

    I'd like to suggest you take a look at the following blog about using date function to manually define filter range to achieve rolling calculations.  They should more accuracy and have better customization than time intelligence functions:

    Time Intelligence "The Hard Way" (TITHW)  

    Regards,

    Xiaoxin Sheng

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    To have rolling, always prefer to have a date table and use Time Intelligence.

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))  

Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))

Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-2,month)),-3,MONTH))

Rolling 3 till last month = 
Var _max = maxx('Date',ENDOFMONTH(dateadd('Date'[Date],-1,month)))
return
CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_max,-3,MONTH))
//CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],_max,-3,MONTH))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
    In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
    Proud to be a Datanaut Connect on Linkedin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi PowerPirate,

    I'd like to suggest you take a look at the following blog about using date function to manually define filter range to achieve rolling calculations.  They should more accuracy and have better customization than time intelligence functions:

    Time Intelligence "The Hard Way" (TITHW)  

    Regards,

    Xiaoxin Sheng