Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Fusilier2
Advocate II
Advocate II

Relative dates on chart

I basically just want a chart to automatically display just the last 12 available monthly data points on a chart.

I've tried setting the filter on the visual to 'Relative date' and in the 'last 12 months':

relative.PNG

But it is showing 11 months

months.PNG

I presume this is because it is working off the current date in the system (Today is 24th January) but there isn't a data point for January in my data yet. So how do I get the chart to just show the last 12 data points (Jan 2024-Dec 2024) and then, when I have a number for January it will automatically show Feb 2024-Jan 2025) without me having to change the filter.

3 ACCEPTED SOLUTIONS
danextian
Super User
Super User

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" )

 

 

danextian_0-1737626644962.png

danextian_1-1737626669070.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

Jihwan_Kim
Super User
Super User

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).

 

Jihwan_Kim_1-1737627107614.png

 

 

Jihwan_Kim_0-1737627096998.png

 

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 ) )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

v-xuxinyi-msft
Community Support
Community Support

Hi @Fusilier2 

 

Thanks for the reply from danextian and Jihwan_Kim .

 

The following test is for your reference.

 

My sample:

vxuxinyimsft_0-1737697780257.png

 

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.

vxuxinyimsft_1-1737698043080.png

 

Output:

 

vxuxinyimsft_5-1737698616943.png

 

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

vxuxinyimsft_3-1737698407468.png

 

Output:

vxuxinyimsft_4-1737698506511.png

 

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.

View solution in original post

3 REPLIES 3
v-xuxinyi-msft
Community Support
Community Support

Hi @Fusilier2 

 

Thanks for the reply from danextian and Jihwan_Kim .

 

The following test is for your reference.

 

My sample:

vxuxinyimsft_0-1737697780257.png

 

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.

vxuxinyimsft_1-1737698043080.png

 

Output:

 

vxuxinyimsft_5-1737698616943.png

 

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

vxuxinyimsft_3-1737698407468.png

 

Output:

vxuxinyimsft_4-1737698506511.png

 

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.

Jihwan_Kim
Super User
Super User

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).

 

Jihwan_Kim_1-1737627107614.png

 

 

Jihwan_Kim_0-1737627096998.png

 

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 ) )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

danextian
Super User
Super User

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" )

 

 

danextian_0-1737626644962.png

danextian_1-1737626669070.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!