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. 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"
            )
        )
    )
Need Help
Show and Tell

7 Replies

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

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

      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. 

  • Angith_Nair's avatar
    Angith_Nair
    Continued Contributor
    1 year ago

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

      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. 

       

  • 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.