CALENDAR TABLE - DAX Last 12 months

[branndicoot]

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,: the last dataset I have is Nov16 - Oct17
• Tomorrow I recieve Nov17, which then needs to change my report year to Dec16 - Nov 17
• Similary when I recieve Dec 17, I will need to change my report year to Jan17-Dec17

The forums mainly make refernece to Fiscal Years in the calendar table

As you can see, this is different from a fiscal year, as my REPORTING YEAR CHANGES EACH MONTH

(months are not set, like a fiscal, but change month to month)

Please advise best practice here 

I am hoping for DAX that does not reference 365 days (as my data goes back 7 years and leap years are a problem)

And DAX I will only have to enter once, not update each month

This is my first PowerBI report I am building, and I am learning DAX slowly

Any and all assistance would be greatly appreciated!

[v-xjiin-msft · Accepted solution]

@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]

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)