Forum Discussion

branndicoot's avatar
branndicoot
Regular Visitor
8 years ago
Solved

CALENDAR TABLE - DAX Last 12 months

Greetings from sunny South Africa! Please can someone help me with my first forum post.   I receive MONTHLY data updates. On refresh, I need my report year to adjust accordingly  For exmaple,: ...
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    branndicoot

     

    In your scenario, you want to create the calendar table dynamically. Right?

     

    First you should know that we can use TODAY() function to get current date. Then to achieve your requirement, you can refer to following DAX:

     

    1. To create a 7 years calendar table based on today:

     

    Table = CALENDAR(DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY())),TODAY())

    2. Get last 12 month:

     

    Last 12 month = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

     

    Also, you can change the date format in Data pane.

     

     

    Thanks,
    Xi Jin.

v-xjiin-msft's avatar
v-xjiin-msft
Icon for Solution Sage rankSolution Sage
8 years ago

branndicoot

 

In your scenario, you want to create the calendar table dynamically. Right?

 

First you should know that we can use TODAY() function to get current date. Then to achieve your requirement, you can refer to following DAX:

 

1. To create a 7 years calendar table based on today:

 

Table = CALENDAR(DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY())),TODAY())

2. Get last 12 month:

 

Last 12 month = DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

 

Also, you can change the date format in Data pane.

 

 

Thanks,
Xi Jin.

james_m's avatar
james_m
Frequent Visitor
8 years ago

This will filter the fact based on the date dimension. Worked for me

 

Amount-Previous2Years = 
VAR Months = -24
VAR FromDate = CALCULATE(EOMONTH(MAX(DimDate[Full Date])+1, Months), FctSales)
RETURN
CALCULATE(SUM(FctSales[Amount]), DimDate[Full Date]>=FromDate)