Need Help - How to get a dax to keep only last 12 months visible in visual

[MacedoLuiss]

Hi, I have a visual that i am computing total number of customer over the last 12 months. Currently, I am filtering the chart using visual level filter pane as per the image

This way i get it filtered. Is there a way to do this purely with dax?
I have a dim_date 1 -> * fact_table, using date_key for relationship. The measure im computing num of customer for LT 12m is:

Total No. Customers (12M) =

VAR __LastDate =

    LASTDATE('Dim Date'[Date])

VAR _12mBack =

    EDATE(__LastDate, -12)

VAR _peridod =

DATESBETWEEN(

    'Dim Date'[Date],

    _12mBack,

    __LastDate

)

VAR _Calc =

IF(

    [Total No. Customer],

    CALCULATE(

            [Total No. Customer],

            _peridod

    )

)

RETURN _Calc

[v-pgoloju]

Hi @MacedoLuiss,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @MacedoLuiss,

Just checking in to see if the solutions shared by community members helped in resolving the issue. 

If any of the responses addressed your concern, please consider marking one as the Accepted Solution. Feel free to reach out if you need further assistance or clarification.

Best regards,
Prasanna Kumar

[Ashish_Mathur]

Hi,

See if my solution in the attached file helps.

[v-pgoloju]

Hi @MacedoLuiss,

Thank you for reaching out to the Microsoft Fabric Forum Community. and also thanks to @Jihwan_Kim and @danextian for prompt and helpful solutions.

 Just checking in to see if the suggestions shared by @Jihwan_Kim  and @danextian  helped with your query on writing a DAX expression to display only the last 12 months in a visual.

If any of the responses helped solve your problem, please consider marking it as the Accepted Solution. Let us know if you need any further support or clarification.

Best regards,
Prasanna Kumar

[Jihwan_Kim]

Hi,

I tried to create a sample pbix file like below, and please check the below picture and the attached pbix file.

Total No. Customers (12M) =
VAR _datalastdate =
    CALCULATE ( MAX ( transaction_fact[date] ), REMOVEFILTERS () )
VAR _12backfromdatalastdate =
    EDATE ( _datalastdate, -12 )
VAR _result =
    IF (
        MAX ( calendar_dimension[Date] ) <= _datalastdate
            && MIN ( calendar_dimension[Date] ) >= _12backfromdatalastdate,
        VAR __LastDate =
            LASTDATE ( 'calendar_dimension'[Date] )
        VAR _12mBack =
            EDATE ( __LastDate, -12 )
        VAR _peridod =
            DATESBETWEEN ( 'calendar_dimension'[Date], _12mBack, __LastDate )
        VAR _Calc =
            IF ( [Total No. Customer], CALCULATE ( [Total No. Customer], _peridod ) )
        RETURN
            _Calc
    )
RETURN
    _result

[danextian]

Hi @MacedoLuiss 

Create a rolling month number column in your calendar table. The sample DAX calc column below returns 1 for the latest month

Rolling Month =
ABS ( DATEDIFF ( MAX ( Dates[Date] ), Dates[Date], MONTH ) - 1 )