Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I'm struggling to work out (so seeking help for) a Power Query or DAX Calc, to work the maximum sales value is for each Employee based on a 20 day rolling period. The data table is shown below. So for Employee 1, it would be dates 27/01/2018 and 14/02/2018 with a total sales for 120000
Employee, Date, Sales
Employee1, 20/01/2018,10000
Employee1, 27/01/2018,30000
Employee1, 14/02/2018,90000
Employee1, 11/03/2018,50000
Employee1, 19/03/2018,20000
Employee1, 20/03/2018,4000
Employee2, 20/01/2018,2000
Employee2, 22/02/2018,3000
Employee2, 24/02/2018,60000
Employee2, 07/03/2018,50000
Employee3, 28/02/2018,1000
Employee3, 20/03/2018,70000
Thanks in advance
Alex
Solved! Go to Solution.
Hi @athomp15,
Try the formula below.
Rolling 20 days = CALCULATE ( SUM ( Sales[ Sales] ); FILTER ( ALL ( Sales[ Date] ); Sales[ Date] >= MAX ( Sales[ Date] ) - 20 && Sales[ Date] <= MAX ( Sales[ Date] ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsWorks Perfectly MFelix,
Many thanks
Alex
Hi @athomp15,
Try the formula below.
Rolling 20 days = CALCULATE ( SUM ( Sales[ Sales] ); FILTER ( ALL ( Sales[ Date] ); Sales[ Date] >= MAX ( Sales[ Date] ) - 20 && Sales[ Date] <= MAX ( Sales[ Date] ) ) )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
86 | |
78 | |
54 | |
39 | |
35 |
User | Count |
---|---|
102 | |
84 | |
48 | |
48 | |
48 |