Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Last 12 month from current one

Hello,

Is there a way to display last 12 month from current month from sales table? So if its current month of Feb as for today, it should show Mar 2022 - Feb 2023.

when Mar 23 become current month Apr 2022-Mar 2023? I have date table also. In date table max date will current date. .

I have to create rolling chart and for that i have to implement a filer by creating a new automated column in date table.

Thanks !

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    According to your statement, I know you want show data from Feb 2022 to Feb 2023, if today is in Feb 2023. I think your requirement is to show 13 months instead of 12 months.

    If you want to show 12month data, it will show you data from Mar 2022 to Feb 2023. I think amitchandak's code will work.

    If you don't want to add a Date table in your table, try this code.

    Last 12 months Today = 
var _max= eomonth(today(),0)
var _min= eomonth(_max,-12 ) +1
return
CALCULATE(SUM('Table'[Value]), FILTER('Table','Table'[Date] >=_min && 'Table'[Date] <= _max))

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , if there is not date filter, try a meausre like

     

    Last 12 months Today =
    var _max= eomonth(today(),0)
    var _min= eomonth(_min,-12 ) +1
    return
    CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      There is a active year filter on the existing chart. Currently the chart is showing from Jan 2022 to current month which is 12 month. Wherreas I want to modify the chart in such a way that when we come to feb 2023 the chart should automatically(after refresh) show the data from Feb 2022 to Feb 2023. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

         

        According to your statement, I know you want show data from Feb 2022 to Feb 2023, if today is in Feb 2023. I think your requirement is to show 13 months instead of 12 months.

        If you want to show 12month data, it will show you data from Mar 2022 to Feb 2023. I think amitchandak's code will work.

        If you don't want to add a Date table in your table, try this code.

        Last 12 months Today = 
var _max= eomonth(today(),0)
var _min= eomonth(_max,-12 ) +1
return
CALCULATE(SUM('Table'[Value]), FILTER('Table','Table'[Date] >=_min && 'Table'[Date] <= _max))

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.