MTD formula with 0 value of orders

[FR14]

Hi Everyone 🙂

For MTD i use following formula, but the problem is that when the value of orders are 0 in a month (for example March) it shows values of previous month instead of showing blank or 0. Could anyone help modify this Dax to show 0 or blank when there is no order in a month?

Order_Subtotal_CY_MTD = CALCULATE(SUM(OrderLine[Booking USD]),DATESMTD(OrderLine[Open_Date]))

Thank you so much in advance!

[v-jiewu-msft · Accepted solution]

Hi @FR14 ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate.

Order_MTD = IF(
    ISBLANK(
        CALCULATE(
            SUM(OrderLine[Booking USD]),
            DATESMTD(OrderLine[Date])
        )
    ), 
    0, 
    CALCULATE(
        SUM(OrderLine[Booking USD]),
        DATESMTD(OrderLine[Date])
    )
)

3.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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

[vivek31 · Accepted solution]

hi @FR14 ,

try this ,

MTD BLANK = CALCULATE(SUM(OrderLine[Booking USD]),DATESMTD(OrderLine[Date])) + 0

[v-jiewu-msft · Accepted solution]

Hi @FR14 ,

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate.

Order_MTD = IF(
    ISBLANK(
        CALCULATE(
            SUM(OrderLine[Booking USD]),
            DATESMTD(OrderLine[Date])
        )
    ), 
    0, 
    CALCULATE(
        SUM(OrderLine[Booking USD]),
        DATESMTD(OrderLine[Date])
    )
)

3.Drag the measure into the table visual. The result is shown below.

Best Regards,

Wisdom Wu

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