Month to Date Null Issue

[Ang007]

I've been struggling with a month-to-date formula. The problem is that, when there are no sales for the current month, the formula displays the latest available data instead of showing zero. Interestingly, it works correctly on the total value, but when applied in a table with categorical breakdowns, this issue arises. I've tried several adjustments without success. The DAX code is mentioned below, hope to get support from my expert friends, unfortunetly, i cannot share the data or pbix. 

MTD sales Corporate =

VAR LatestDate = MAX('Lease Ledgers'[Lease Created On])

VAR currentMonthStart = DATE(YEAR(LatestDate), MONTH(LatestDate), 1)

RETURN

    IF(

        ISBLANK(

            CALCULATE(

                SUM('Lease Ledgers'[Fin Amt '000]),

                FILTER(

                    'Lease Ledgers',

                    'Lease Ledgers'[Lease Created On] >= currentMonthStart &&

                    'Lease Ledgers'[Lease Created On] <= LatestDate &&

                    'Lease Ledgers'[corp or retail] = "CORPORATE"

                )

            )

        ),

        0,

        CALCULATE(

            IF(

                ISBLANK(SUM('Lease Ledgers'[Fin Amt '000])),

                0,

                SUM('Lease Ledgers'[Fin Amt '000])

            ),

            FILTER(

                'Lease Ledgers',

                'Lease Ledgers'[Lease Created On] >= currentMonthStart &&

                'Lease Ledgers'[Lease Created On] <= LatestDate &&

                'Lease Ledgers'[corp or retail] = "CORPORATE"

            )

        )

    )

[v-yohua-msft]

Hi, @Ang007 

Based on your information, I create a sample table:

Then create a new measure, and try the following dax expression:

MTD_sales_Corporate = 
VAR LatestDate = MAX('Table'[Lease Created On])
VAR currentMonthStart = DATE(YEAR(LatestDate), MONTH(LatestDate), 1)
VAR currentMonthEnd = EOMONTH(LatestDate, 0)
VAR MTD_Sales = 
    CALCULATE(
        SUM('Table'[Fin Amt '000']),
        FILTER(
            'Table',
            'Table'[Lease Created On] >= currentMonthStart &&
            'Table'[Lease Created On] <= currentMonthEnd &&
            'Table'[corp or retail] = "CORPORATE"
        )
    )
RETURN
    IF(
        ISBLANK(MTD_Sales),
        0,
        MTD_Sales
    )

This formula ensures that only the data for the current month is calculated and does not include the data of the previous month. Assuming that today is November 12, 2024, the formula will calculate the sum of business sales from November 1, 2024 to November 12, 2024(The sum of the eligible items from the 1st to the 12th is 1350, and the one for October is not calculated). Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Ang007]

Hi Yohua, 
thank you very much for the try, But unfortunetly i'm still getting this, may be this is complicated becasue i've used slicers and all this is just a part of my dashboard, I'm seriously wondering why it shows correctly when i use it as total,

[v-yohua-msft]

Hi, @Ang007 

I'm trying to re-understand your problem. So I recreated the simple example table:

Then create a new measure:

MTD sales Corporate = 
VAR LatestDate = MAX('Lease Ledgers'[Lease Created On])
VAR currentMonthStart = DATE(YEAR(LatestDate), MONTH(LatestDate), 1)
VAR SalesAmount = 
    CALCULATE(
        SUM('Lease Ledgers'[Fin Amt '000]),
        FILTER(
            'Lease Ledgers',
            'Lease Ledgers'[Lease Created On] >= currentMonthStart &&
            'Lease Ledgers'[Lease Created On] <= LatestDate &&
            'Lease Ledgers'[corp or retail] = "CORPORATE"
        )
    )
RETURN
    IF(
        ISBLANK(SalesAmount),
        0,
        SalesAmount
    )

At the same time, I also created a calculated column that represents the month for a better view

Put measure and Month field in matrix visual, you can see the following preview:

The subtotals of the matrix are the sum of the values for the current month. The maximum total is the sum of the current months, not the sum of November and December.

You can switch slicers to see the sum of November or December

This is my understanding of your problem, you can correct it if it is not, and you can also use sample data to show your expected results.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Angith_Nair]

Hi @Ang007 

Use the below DAX:

MTD sales Corporate =
VAR LatestDate = MAX('Lease Ledgers'[Lease Created On])
VAR currentMonthStart = DATE(YEAR(LatestDate), MONTH(LatestDate), 1)
VAR MTD_Sales = 
    CALCULATE(
        SUM('Lease Ledgers'[Fin Amt '000]),
        'Lease Ledgers'[Lease Created On] >= currentMonthStart &&
        'Lease Ledgers'[Lease Created On] <= LatestDate &&
        'Lease Ledgers'[corp or retail] = "CORPORATE"
    )
RETURN
    IF(
        ISBLANK(MTD_Sales),
        0,
        MTD_Sales
    )

[Ang007]

Hi Angith,
Thanks for the reply but still I get the issue, V1 is your one, in here i get previous months data in this table. 

[saud968]

Try the below measure

MTD sales Corporate =
VAR LatestDate = MAX('Lease Ledgers'[Lease Created On])
VAR currentMonthStart = DATE(YEAR(LatestDate), MONTH(LatestDate), 1)
VAR SalesAmount =
CALCULATE(
SUM('Lease Ledgers'[Fin Amt '000']),
FILTER(
'Lease Ledgers',
'Lease Ledgers'[Lease Created On] >= currentMonthStart &&
'Lease Ledgers'[Lease Created On] <= LatestDate &&
'Lease Ledgers'[corp or retail] = "CORPORATE"
)
)
RETURN
IF(
ISBLANK(SalesAmount),
0,
SalesAmount
)

In this version, I introduced a variable SalesAmount to store the calculated sales amount. This helps simplify the logic and ensures that the ISBLANK check is applied directly to the calculated sales amount. This should help in returning zero when there are no sales for the current month.


Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Ang007]

Hi Saud,
Thanks for the reply but still I get the issue, V1 is your one (Replaced your DAX her, in here i get previous months data in this table.