Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Help

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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