Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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':
But it is showing 11 months
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.
Solved! Go to Solution.
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" )
Proud to be a 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).
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.
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.
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.
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 ) )
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.
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" )
Proud to be a Super User!