Relative dates on chart

Hi Fusilier2 

Add a column in your calendar table to check if a specific date falls within 12 months from the latest date. In the screenshot below, the latest date is today's date, which is the maximum date. The calculated column compares the current row date with the _MaxDate variable. If they're in the same month, the result is 0, hence the +1. Any date within the last 12 months gets a "Yes" for being within the timeframe.

 

Rolling 12 months = 
VAR _MaxDate =
    MAX ( DatesTbl[Date] )
RETURN
    IF ( DATEDIFF ( DatesTbl[Date], _MaxDate, MONTH ) + 1 <= 12, "Yes", "No" )

 

 

 

 

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

One of ways is to create a measure something like below, and then it will only describe the recent 12 months data (not by today date's recent 12 months period, but by the latest-data-exist-date's recent 12 months period).

 

 

 

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

Sales recent 12 months: =
VAR _maxdatesales =
    CALCULATE ( MAX ( sales[date] ), REMOVEFILTERS ( 'calendar' ) )
VAR _calendarlimit =
    SUMMARIZE (
        FILTER ( ALL ( 'calendar' ), 'calendar'[Date] <= _maxdatesales ),
        'calendar'[Year-Month],
        'calendar'[Year-Month sort]
    )
VAR _window =
    WINDOW (
        1,
        ABS,
        12,
        ABS,
        _calendarlimit,
        ORDERBY ( 'calendar'[Year-Month sort], DESC )
    )
RETURN
    CALCULATE ( [Sales total:], KEEPFILTERS ( _window ) )

 

Hi Fusilier2 

 

Thanks for the reply from danextian and Jihwan_Kim .

 

The following test is for your reference.

 

My sample:

 

Create a measure as follows

Measure = 
VAR _maxDate = CALCULATE(MAX('Table'[Date]), ALL('Table'))
VAR _eomonth = EOMONTH(_maxDate, - 12) + 1
RETURN
IF(MAX('Table'[Date]) >= _eomonth, 1, 0)

 

Put the measure into the visual-level filters, set up show items when the value is 1.

 

Output:

 

 

I've also added January's data for testing as follows:

 

Output:

 

Best Regards,
Yulia Xu

 

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