DAX Help

[Anonymous]

I would like to create DAX which pulls last 12 Months data based on selected date.

Example:

If user selects the current date June 10th ,2021 ,it should display last 12 months of data along with 10 Days of the June 2021 Month as well.

Basically ,The data should show in the dashboard  from June 1st 2020 to till date. When Apply the relative Date (is the last 12 months filter) option in the powerBI ,it showing from June 11th 2020 till date.

[v-shex-msft · Accepted solution]

HI @Anonymous,

I'd like to suggest you create a measure filter with custom expressions to filter records and use it on the visual level filter.

Applying a measure filter in Power BI - SQLBI

Measure =
VAR selected =
    MAX ( NewTable[Date] )
RETURN
    IF (
        MAX ( Table[Date] )
            IN CALENDAR (
                DATE ( YEAR ( selected ) - 1, MONTH ( selected ), DAY ( selected ) ),
                selected
            ),
        1,
        0
    )

Regards,

Xiaoxin Sheng

[lbendlin]

A cheap version would be "in the last 365 days"  if you can live with the leap year issues.