Forum Discussion

Joshua_'s avatar
Joshua_
Icon for Helper II rankHelper II
3 years ago

Using the TOTALMTD function but only on business days

Hi guys,


I am trying to create a measure that allows me to accumulate the sales for the month, so I am using the TOTALMTD function, this works correctly on the filtered month (September).

I also want to get the previous month (August), so I add the filter DATEADD(CALENDARIO[Date],-1,month) , however the value is repeated when it is NOT a weekday in August but it is a weekday in September .

 

I have a calendar table with business days and related to the sales table through dates.

 

Example:

 

 

 

 

expectation:

 

My measures:

 

September = SUM(FCT_VENTAS[MONTO_K]),
 
August = 
CALCULATE(
    SUM(FCT_VENTAS[MONTO_K]),
    DATEADD(CALENDARIO[Date],-1,month)
)
 

TOTALMTD (sep) =

TOTALMTD(
        SUM(FCT_VENTAS[MONTO_K]),
        CALENDARIO[Date]
    )
 
TOTALMTD (ago) =
TOTALMTD(
    SUM(FCT_VENTAS[MONTO_K]),
    DATEADD(CALENDARIO[Date],-1,month)  
)
 
 
thank a lot!

2 Replies