Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nanma94
Helper III
Helper III

Rolling Total to prior month end

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Dale! 

I got caught up with EOMonth, vs endofmonth. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.