Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to get sum rolling 12 months sales. For example if today is 09/26/2019 then I need to get the sum of sales from 09/27/2018 to 09/26/2019 ( till today). Can some help me with the proper DAX code here.
Solved! Go to Solution.
Way 1
Sales 365 Days = ( VAR _Cuur_start = Max('Date'[Date]) -365 VAR _Curr_END = Max('Date'[Date]) return calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END ) )
Way 1
Sales 365 Days = ( VAR _Cuur_start = Max('Date'[Date]) -365 VAR _Curr_END = Max('Date'[Date]) return calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END ) )
Thanks a lot @amitchandak with few changes its working fine.
I am also trying to calculate sales in the similar manner for month. But I believe -30 will not solve this as it will miss the data where months end on 31. Lest say I want to calculate the rolling sum of monthly sales so today it should give me sum of sales from 08/26/2019 to 09/26/2019 and tomorrow 08/27/2019 to 09/27/2019 and so on any idea how to achieve this ?
is -1 month not working as expected.
Sales 12 Months = ( var _cuur_start_2 = maxx('Date',DATEADD('Date'[Date],-1,MONTH)) VAR _Curr_END = Max('Date'[Date]) return calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _cuur_start_2 && Sales[Sales Date] <= _Curr_END ) )
Way 2
Sales 12 Months = ( var _cuur_start_2 = maxx('Date',DATEADD('Date'[Date],-12,MONTH)) VAR _Curr_END = Max('Date'[Date]) return calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _cuur_start_2 && Sales[Sales Date] <= _Curr_END ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |