Page To show last 12 months of available data.

[NewbieJono]

Normally, I would filter a page to show the past 12 calendar months.

is there a method to apply to show the past 12 months of available data based on the max date of the date? in my case, currently, the max date of data is 01/07/2024. therefore I would like to show 01/08/2023 - 01/07/2024.

I need this to be dynamic, and as data gets added, it will update the 12-month period.

[v-nuoc-msft]

Hi @NewbieJono 

Thank you very much parry2k and lbendlin for your prompt reply. Allow me to add a few things.

Here's some dummy data

“Table”

"Date"

Date = CALENDAR("9/1/2024", TODAY())

Create a measure.

Last 12 Months = 
VAR MaxDate = MAX('Date'[Date])
RETURN 
IF(
    SELECTEDVALUE('Table'[Date]) >= EDATE(MaxDate, -11) + 1
    &&
    SELECTEDVALUE('Table'[Date]) <= MaxDate,
    1,
    0
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

therefore I would like to show 01/08/2023 - 01/07/2024.

That's only 11 months and a day.

[NewbieJono]

Yes sorry but if i was showing on a chart, it would **bleep** up 12 months.... if that makes sense. 

basically the data only ever has data for the 1st of a month

[parry2k]

@NewbieJono assuming you have a calendar table and a sales table and based on last date on the same table you want to show last 12 months data. Here is the measures:

Last 12 Month Sales = 
VAR __MaxDate = CALCULATE ( LASTDATE ( 'Sales'[Date] ), ALL ( ) )
RETURN
CALCULATE (
    SUM ( 'Sales'[Amount] ),
    KEEPFILTERS ( DATESINPERIOD ( 'Calendar'[Date], __MaxDate, -12, MONTH ) )
)

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist