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

Calculated column "Rolling 3M SALES" should result as 6 as it is within 3M from Date June 1st 2023.

But the calculated column is not transforming from row context to filter context

 

 

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

 

 

Thanks.

 

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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?