Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a DAX formula I want to calculate the rolling total unit up to the end of the prior month. However, the below formula wont give me the unit count until the last day of the prior month. Rather, it gives the count until the similiar day of the current month. For example, if the current month is June 2018 - so the last day of the month is 6/30/2018, , this formula below gives count until 5/30/2018, missing 5/31/2018.
M_RollingAllUnitsPriorMonth = CALCULATE(SUM(SalesActual[Units]), FILTER(ALL(DateTable[Date]), DateTable[Date]<=EDATE(MAX(DateTable[Date]),-1)))
How to push EDATE(MAX(DateTable[Date]),-1) to the last day of prior month?
Thank you so much!
NM
Solved! Go to Solution.
Hi @nanma94,
Try the function dax/eomonth-function-dax please.
M_RollingAllUnitsPriorMonth = CALCULATE ( SUM ( SalesActual[Units] ), FILTER ( ALL ( DateTable[Date] ), DateTable[Date] <= EOMONTH ( MAX ( DateTable[Date] ), -1 ) ) )
Best Regards,
Dale
Hi @nanma94,
Try the function dax/eomonth-function-dax please.
M_RollingAllUnitsPriorMonth = CALCULATE ( SUM ( SalesActual[Units] ), FILTER ( ALL ( DateTable[Date] ), DateTable[Date] <= EOMONTH ( MAX ( DateTable[Date] ), -1 ) ) )
Best Regards,
Dale
Thank you Dale!
I got caught up with EOMonth, vs endofmonth.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |