Forum Discussion

Ang007's avatar
Ang007
New Member
1 year ago

Month to Date Null Issue

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. Interest...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Ang007
New Member
1 year ago

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,

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.