Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 12 Month Names in Table/Matrix

@ links to members, content

Why are things to hard in Power BI.  I would like to display the last 12 month names from current date in my table.  So my current date is 7/11/2023, This table should only show 12 months back.  The correct amounts will show when I get the month names correct. 

 

Jul7852346
Aug

7285543

Sept7912356
Oct8451233
Nov2548658
Dec7898856
Jan7849848
Feb7849597
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , if no date is selected then you can have measure like

     

    new measure =
    var _max = today()
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max)) //assuming you are using date table

     

    But if you want it based on slicer selection then the slicer needs to be on an independent date table

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , if no date is selected then you can have measure like

     

    new measure =
    var _max = today()
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max)) //assuming you are using date table

     

    But if you want it based on slicer selection then the slicer needs to be on an independent date table

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.