Forum Discussion

ak77's avatar
ak77
Icon for Post Patron rankPost Patron
3 years ago

Calculate Sum with multiple filters(Visual filter and select filter)

Hi all,

I have the below dataset with a select Date option

I need help in DAX calculation for below :

 

1.user selects a date 

2. The DAX should take past one yr data from the Selected Date

3.Calculate the sum of values for all combination of (product and entity) for one year filtered in first step and display the sum.

 

Ex: user selects 01/01/2018

DAX-> take data from 01/01/2018 to 01/01/2017

Calculate sum for all combination of Product and Entity( A-P, B-I, C-P.....) for the date range and display the sum

 

Please help if possible

 

Need Help
Show and Tell

2 Replies

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

    ak77 , With help from date table

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

     

    Rolling Months Formula: https://youtu.be/GS5O4G81fww

     

    If you need trend of 12 months you need an independent date table for slicer

    //Date1 is an 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