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

[DineshArivu]

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,

        1

    )

RETURN

    Result

Please help me on this.

Thanks

DK

[Praful_Potphode]

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.

[DineshArivu · Accepted solution]

@Everyone

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

[v-saisrao-msft]

Hi @DineshArivu,

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

Thank you.

[v-saisrao-msft]

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-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[v-saisrao-msft]

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.

[Jihwan_Kim]

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

[DineshArivu]

@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 

[Jihwan_Kim]

Hi,

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

Thank you.