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

[Rai_BI]

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

[v-kaiyue-msft · Accepted solution]

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.

[v-kaiyue-msft · Accepted solution]

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.

[Rai_BI · Accepted solution]

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)

[lbendlin]

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

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/1447...