Forum Discussion

rajasekar_o's avatar
rajasekar_o
Icon for Helper V rankHelper V
1 year ago

LY MTD

I have sales data from 
1-1-2022 to 25-09-2024
Sales Table  have the column
invno,invdate,item,qty,Netamount
i have calender table 
start date :1-1-2024end date: 31-12-2024

AND MTD calculation is 

MTD Sale _new =
VAR _year = VALUES('date'[Year])
VAR _month = VALUES('date'[month no])
RETURN
IF(
    ISFILTERED('date'[Year]) && ISFILTERED('date'[month no]),
    CALCULATE(
        SUM('sales'[Amount]),
        FILTER(
            'Sales',
            YEAR('sales'[Inv Date]) in _year
            &&
            MONTH('sales'[Inv Date]) IN _month
        )
    ),
    TOTALMTD(
        SUM('sales'[Amount]),
        'sales'[Inv Date]
    )
)
now i want to calculate LY MTD SALES 
IF i didn't filter any month it need to show Curent month of  LYMTD 
IF I select any multiple month it need to show that selected month savel value
i try CALCULATE LYMTD :  ist show wrong value
CALCULATE([MTD Sale _new],
    SAMEPERIODLASTYEAR('Date'[Date])
)
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi rajasekar_o -Your existing MTD Sale _new measure works fine.

    Here's the LY MTD Sale measure as below:

    LY MTD Sales =
    VAR _CurrentYear = VALUES('Date'[Year])
    VAR _CurrentMonth = VALUES('Date'[Month No])
    VAR _IsMonthFiltered = ISFILTERED('Date'[Month No])

    RETURN
    IF(
    _IsMonthFiltered,
    // When months are selected, use the selected months for LY MTD
    CALCULATE(
    [MTD Sale _new],
    SAMEPERIODLASTYEAR('Date'[Date])
    ),
    // When no month is selected, calculate LY MTD for the current month
    CALCULATE(
    SUM('Sales'[Netamount]),
    DATESBETWEEN(
    'Date'[Date],
    STARTOFMONTH(SAMEPERIODLASTYEAR(TODAY())),
    TODAY()
    )
    )
    )

    Showing LY MTD for the selected period or defaulting to the current month’s LY MTD. Hope this works.

     

      • rajendraongole1's avatar
        rajendraongole1
        Icon for Super User rankSuper User
        1 year ago

        Hi rajasekar_o - try the below logic

        LY MTD Sales =
        VAR _IsMonthFiltered = ISFILTERED('Date'[Month No])
        RETURN
        IF(
        _IsMonthFiltered,
        // If a month filter is applied, calculate LY MTD for the selected months
        CALCULATE(
        [MTD Sale _new],
        SAMEPERIODLASTYEAR('Date'[Date])
        ),
        // If no month is selected, show LY MTD for the current month
        CALCULATE(
        SUM('Sales'[Netamount]),
        DATESBETWEEN(
        'Date'[Date],
        STARTOFMONTH(SAMEPERIODLASTYEAR(TODAY())),
        TODAY()
        )
        )
        )

         

        still issue exist, please share sample data pbix file.

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    1 year ago
    1. No month is selected: Show LY MTD sales for the current month of the previous year.
    2. Multiple months are selected: Show the LY MTD sales for the selected months in the previous year.

    You can try this updated measure for LY MTD:

     

    LY MTD Sale =
    VAR _year = VALUES('date'[Year])
    VAR _month = VALUES('date'[month no])
    RETURN
    IF(
    ISFILTERED('date'[Year]) && ISFILTERED('date'[month no]),
    CALCULATE(
    [MTD Sale _new],
    SAMEPERIODLASTYEAR('date'[Date])
    ),
    CALCULATE(
    TOTALMTD(
    SUM('sales'[Amount]),
    'sales'[Inv Date]
    ),
    SAMEPERIODLASTYEAR('date'[Date])
    )
    )

     

    This approach should give you the correct LY MTD values both when filtering specific months and when no month is selected.