cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ryank Frequent Visitor
Frequent Visitor

TOTALMTD error when future values exist

I have a weird problem where the TOTALMTD calc is not working as there are records in the table with a Date value in the future (ie beyond today). So it seems to get confused when presenting the output value.

 

To work around this I filter these records out using the Date field on a Page level filter and everything works fine. I'm now trying to replicate this in the Measure with DAX so it can be dynamic (ie look at data before tomorrow but include today) but I'm not getting the same result.

 

Here is the filter I used as the test 

 

Capture.JPG

shouldn't the following do the trick? 

 

Total Billing MTD = TOTALMTD ( 
SUM(BillingFact[Billed]), BillingFact[Date (daily)], 
FILTER(BillingFact, BillingFact[Date] < TODAY() +1)
)

 

 

1 REPLY 1
Community Support Team
Community Support Team

Re: TOTALMTD error when future values exist

Hi @ryank,

 

Please try this formula:

Total Billing MTD =
TOTALMTD (
    CALCULATE (
        SUM ( BillingFact[Billed] ),
        FILTER ( BillingFact, BillingFact[Daily Date] < TODAY () + 1 )
    ),
    BillingFact[Daily Date]
)

Regards,
Yuliana Gu

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