Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic separate tables

Hi,   Is there a way to create a number of separate tables or matrixes which are dynamic so that they change along with the months.  I'd like to have a rolling 3 months but all three months on sep...
Data Protection
General Comment
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , You need to create measure which are for last month, 2nd last month and 3rd last month and use them in matrix

     

    example

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD("Date"[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd("Date"[Date],-1,MONTH)))
    last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth("Date"[Date]))

     

    2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd("Date"[Date],-2,MONTH)))

     

    3rd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd("Date"[Date],-3,MONTH)))

     

     

    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-1,Month))

     

    2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-2,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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , You need to create measure which are for last month, 2nd last month and 3rd last month and use them in matrix

 

example

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD("Date"[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd("Date"[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth("Date"[Date]))

 

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd("Date"[Date],-2,MONTH)))

 

3rd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd("Date"[Date],-3,MONTH)))

 

 

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-1,Month))

 

2 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-2,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 :radacad sqlbi My Video Series Appreciate your Kudos.

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.