topic Re: MTD formula with 0 value of orders in DAX Commands and Tips

MTD formula with 0 value of orders

FR14 — Wed, 03 Apr 2024 16:53:58 GMT

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!

Re: MTD formula with 0 value of orders

Anonymous — Thu, 04 Apr 2024 01:17:50 GMT

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.

Re: MTD formula with 0 value of orders

vivek31 — Thu, 04 Apr 2024 17:14:46 GMT

hi FR14 ,

try this ,

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