topic Re: Need to create DAX for last 3 month visible in clustered column chart in DAX Commands and Tips

Need to create DAX for last 3 month visible in clustered column chart

DineshArivu — Tue, 09 Sep 2025 13:22:22 GMT

Hi Experts,

I want to create a dax to display the last 3 months when we select any month from the slicer. if not selected then it will display all month data in clustered column chart.
Monthname is a slicer (MonthName = FORMAT([Date],"MMMM")) //text type
Monthyear is a X axis in visual (MonthYear = FORMAT([Date], "MMM-yyyy")) //text type

Tried this measure but no luck:

Last3Months =

VAR HasMonthSelected =

ISFILTERED ( 'CustomCalendar'[MonthName] )

VAR EndDate =

CALCULATE ( MAX ( 'CustomCalendar'[Date] ), ALLSELECTED ( 'CustomCalendar' ) )

VAR SelectedDate =

MAX ( 'CustomCalendar'[Date] )

VAR Result =

SWITCH (

TRUE (),

HasMonthSelected

&& SelectedDate IN DATESINPERIOD ( 'CustomCalendar'[Date], EndDate, -3, MONTH ), 1,

HasMonthSelected, 0,

)

RETURN

Result

Please help me on this.

Thanks

Re: Need to create DAX for last 3 month visible in clustered column chart

Jihwan_Kim — Wed, 10 Sep 2025 02:52:35 GMT

Hi,

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

Please check the below picture and the attached pbix file.

One of ways is to create calculation group, and then put the calculation item into the visual level filter pane.

Create calculation groups in Power BI - Power BI | Microsoft Learn

_cg_filter = VAR _slicerselect = MAX ( 'calendar slicer'[Month-Year sort] ) VAR _t = FILTER ( ALL ( 'calendar'[Month-Year], 'calendar'[Month-Year sort] ), 'calendar'[Month-Year sort] <= _slicerselect ) VAR _window = WINDOW ( 1, ABS, 3, ABS, _t, ORDERBY ( 'calendar'[Month-Year sort], DESC ) ) RETURN SWITCH ( TRUE (), NOT ISFILTERED ( 'calendar slicer'[Month name] ), SELECTEDMEASURE (), ISFILTERED ( 'calendar slicer'[Month name] ), CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( _window ) ), SELECTEDMEASURE () )

Re: Need to create DAX for last 3 month visible in clustered column chart

v-saisrao-msft — Wed, 10 Sep 2025 09:07:50 GMT

Hi DineshArivu,

Have you had a chance to review the solution we shared by Jihwan_Kim? If the issue persists, feel free to reply so we can help further.

Thank you.

Re: Need to create DAX for last 3 month visible in clustered column chart

DineshArivu — Wed, 10 Sep 2025 12:07:52 GMT

Jihwan_Kim thanks for your solution.
few points from my side:
1. We are not using any columns directly like sales,values for the visual X axis (all 5 measures used)

2. as we are using measures, there is no relationship created.
3. I cannot able to apply your measure into visual filter , like the filtering type is different (is,not equals,is blank,no blank).

If i use the same dax as calculated column, then it prompts error

Re: Need to create DAX for last 3 month visible in clustered column chart

Jihwan_Kim — Thu, 11 Sep 2025 02:20:53 GMT

Hi,

Please share your sample pbix file's link, and then I can try to have a look into it.

Thank you.

Re: Need to create DAX for last 3 month visible in clustered column chart

v-saisrao-msft — Thu, 11 Sep 2025 09:21:49 GMT

Hi DineshArivu,

Please provide sample pbix file that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data?

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

Re: Need to create DAX for last 3 month visible in clustered column chart

v-saisrao-msft — Mon, 15 Sep 2025 04:18:17 GMT

Hi DineshArivu,

If the issue persists, please provide the sample pbix file so we can assist in resolving it.

Thank you.

Re: Need to create DAX for last 3 month visible in clustered column chart

Praful_Potphode — Tue, 16 Sep 2025 06:50:30 GMT

HI DineshArivu

you can create a shadow date table which will be used in visualization and filter the original date table.

Step by Step process is mentioned in this article by SQL BI
https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

this shows previous 6 months based on slicer selection.they have used -6 in the calculations.

you can use -3 for previous 3 months.

Please marks the answer as resolved once confirmed.

Re: Need to create DAX for last 3 month visible in clustered column chart

DineshArivu — Tue, 16 Sep 2025 08:14:51 GMT

Anonymous

below dax is works for me ..
thanks

Last 3 months =
VAR HasMonthSelected =
    ISFILTERED ( 'CustomCalendar for slicer'[MonthName] )
VAR EndDate =
    MAX ( 'CustomCalendar for slicer'[Date] )
VAR SelectedDate =
    MAX ( 'CustomCalendar'[Date] )
VAR Result =
    SWITCH (
        TRUE (),
        HasMonthSelected
            && SelectedDate IN DATESINPERIOD ( 'CustomCalendar'[Date], EndDate, -3, MONTH ), 1,
        HasMonthSelected, 0,
        1
    )
RETURN
    Result