topic Re: DAX measure that calculates sales for the last 12 months from a filter in the slicer in DAX Commands and Tips

DAX measure that calculates sales for the last 12 months from a filter in the slicer

Rai_BI — Tue, 27 Feb 2024 12:04:26 GMT

Hi friends!

In Power BI, I need a DAX measure that calculates sales for the last 12 months from a filter in the slicer. For example, if you filter the month "01/2024" in the slicer, I need it to return sales from "02/2023" to "01/2024". I intend to place this measurement in a bar graph that will always return 12 columns, one for each month.

I have a calendar table with dates and a column called "Month/Year" (MM/YYYY) which is related to the sales table by date.

I have the DAX measure below, but it don´t calculate last 12 month when the slicer is filtered.

Last 12M = IF( ISFILTERED('dCalendar'), CALCULATE( [Sales US$], FILTER( 'dCalendar', 'dCalendar'[Date] >= EOMONTH(TODAY(), -12)+1 && 'dCalendar'[Date] <= EOMONTH(TODAY(),0) ) ), [Sales US$] )

Re: DAX measure that calculates sales for the last 12 months from a filter in the slicer

lbendlin — Tue, 27 Feb 2024 21:15:41 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX measure that calculates sales for the last 12 months from a filter in the slicer

Anonymous — Wed, 28 Feb 2024 06:43:36 GMT

Hi Rai_BI ,

1. Create a calculation sheet to get all the dates you need.

Table = DISTINCT('financials'[Date])

2. Create a slicer using dates from the calculation table.

3. Create a measure to determine whether the date in the table is within the twelve months before the selected date.

Measure = VAR _select = SELECTEDVALUE('Table'[Date]) RETURN IF(_select >= MAX('financials'[Date]) && _select <= MAX('financials'[Date]) + 365,1,0)

4. Place the measure value in the filters pane of the visual object to be displayed, and filter the data with a measure value equal to 1.

5. The final result is shown in the figure below.

Maybe your date data needs to be further processed using functions such as EOMONTH, but the overall idea is this. First create a table that has no relationship with the original table to be used as a slicer, and then use the measurement value to filter.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: DAX measure that calculates sales for the last 12 months from a filter in the slicer

Rai_BI — Wed, 28 Feb 2024 17:23:07 GMT

Thank you ! Your solution is very good. But found this video in the link Show last 6 months based on user single slicer selection (youtube.com)

Re: DAX measure that calculates sales for the last 12 months from a filter in the slicer

PBIdashboards — Wed, 24 Jun 2026 03:57:58 GMT

The fix in the accepted solution is correct , replacing TODAY() with MAX('dCalendar'[Date]) respects the slicer context instead of ignoring it.

Clean pattern for rolling 12 months from slicer:

Last 12M Sales =
VAR _maxDate = MAX('dCalendar'[Date])
VAR _minDate = EDATE(_maxDate, -12) + 1
RETURN
CALCULATE(
[Sales US$],
DATESBETWEEN('dCalendar'[Date], _minDate, _maxDate)
)

DATESBETWEEN is cleaner than FILTER(ALL(...)) here because it handles the date table relationship automatically.

For reports where users need to compare rolling 12M vs prior 12M themselves (without the developer creating a separate measure each time), Flexa Tables on AppSource lets Finance users select the two periods to compare directly in the published report period comparison becomes self-service