Forum Discussion

subhashg's avatar
subhashg
Frequent Visitor
2 years ago
Solved

achieving filter context in calculated field

I have 2 tables , Slaes table & Calender. I want to have a calculated feild in sales table which will calculate 3M rolling sum of sales at "Region" level. For example:  For Region HYD  & Iterm- i1...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    subhashg , The one you have given should work as measure

     

    Rolling 3M SALES = CALCULATE(SUM('Sales Table'[Sales]),DATESINPERIOD(Calender[Date],Max('Sales Table'[Date]),-3,MONTH))

     

    another example

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

     

     

    For COlumn

     

    New column =

    var _date = [Sales Date]

    var _3m = date(year(_date), month(_date) -3, day(_date) )

    return

    sumx(filter(Table, [Region] = earlier([Region]) && [Item] = earlier([Item]) && [Sales Date]>= _3m && [Sales Date] <=_date) )

     

    Earlier, I should have known Earlier: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s&list=PLPaNVDMhUXGYU97pdqwoaociLdwyDRn39&index=1

subhashg's avatar
subhashg
Frequent Visitor
2 years ago

amitchandak  This helps. Thanks a lot for your time.

But, Below code fails to work when the current date is 2023 Jan which becomes 2023 Oct instead of 2022 Oct.

var _3m = date(year(_date), month(_date) -3, day(_date) )

 

Is there any fix for it?