Forum Discussion

rajasekar_o's avatar
rajasekar_o
Helper V
1 year ago
Solved

MTD calculation

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

i Calculate MTD 
its showing  blank( MTD function is showing dec month sales)
but i want to calculate CM MTD 

1. IF i didn't filter any month it need to show Curent month  MTD
2. IF I select any multiple month it need to show that selected month savel value

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi rajasekar_o 

     

    Try this:

     

    LYMTD Sales = 
VAR _year = SELECTEDVALUE('Calendar'[Year])
VAR _month = VALUES('Calendar'[Month])
RETURN
IF(
    ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]),
    CALCULATE(
        SUM('Sales'[Netamount]),
        FILTER(
            'Sales',
            YEAR('Sales'[invdate]) = _year - 1
            &&
            MONTH('Sales'[invdate]) IN _month
        )
    ),
    TOTALMTD(
        SUM('Sales'[Netamount]), 
        'Sales'[invdate]
    )
)

     

     

    Regards,

    Nono Chen

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

     

5 Replies

  • 123abc's avatar
    123abc
    Community Champion
    1 year ago

    To address your requirement for calculating Month-to-Date (MTD) values based on whether a month is selected or not, you can use the following DAX measure in Power BI:

     

    MTD Sales =
    VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month], MONTH(TODAY()))
    VAR SelectedYear = SELECTEDVALUE('Calendar'[Year], YEAR(TODAY()))
    RETURN
    CALCULATE(
    SUM(SalesTable[Netamount]),
    DATESBETWEEN(
    'Calendar'[Date],
    DATE(SelectedYear, SelectedMonth, 1),
    TODAY()
    )
    )

     

    Explanation:

    1. SelectedMonth and SelectedYear: These variables will dynamically check if any month is selected. If no month is selected, it defaults to the current month and year using MONTH(TODAY()) and YEAR(TODAY()).
    2. CALCULATE with DATESBETWEEN: This function calculates the sum of Netamount from the first day of the selected or current month up to today (for the current month).

    Behavior:

    • If no month is selected, it will display MTD sales for the current month.
    • If one or more months are selected, it will calculate the MTD for those selected months.
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi rajasekar_o 

     

    Thank you very much 123abc for your prompt reply.

     

    For your question, here is the method I provided:

     

    "Calendar"

     

    Calendar = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date])
)

     

     

    "Sales"

     

    create a measure.

     

    MTD Sales = 
VAR _year = VALUES('Calendar'[Year])
VAR _month = VALUES('Calendar'[Month])
RETURN
IF(
    ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]),
    CALCULATE(
        SUM('Sales'[Netamount]),
        FILTER(
            'Sales',
            YEAR('Sales'[invdate]) in _year
            &&
            MONTH('Sales'[invdate]) IN _month
        )
    ),
    TOTALMTD(
        SUM('Sales'[Netamount]), 
        'Sales'[invdate]
    )
)

     

    Here is the result.

     

    No slicer

    Selective slicer

     

    Regards,

    Nono Chen

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

     

    • rajasekar_o's avatar
      rajasekar_o
      Helper V
      1 year ago

      thank you its working 

      simmularly how to calculate lastyear mtd 
        if i select sep month  LYMTD need to calculate value 
      1-9-2023 to 27-9-2023
      if i select jan or feb month  LYMTD need to calculate value 
      then need to show full month value 
       if i select multiple month then show selected month sales from lastyear




  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi rajasekar_o 

     

    Try this:

     

    LYMTD Sales = 
VAR _year = SELECTEDVALUE('Calendar'[Year])
VAR _month = VALUES('Calendar'[Month])
RETURN
IF(
    ISFILTERED('Calendar'[Year]) && ISFILTERED('Calendar'[Month]),
    CALCULATE(
        SUM('Sales'[Netamount]),
        FILTER(
            'Sales',
            YEAR('Sales'[invdate]) = _year - 1
            &&
            MONTH('Sales'[invdate]) IN _month
        )
    ),
    TOTALMTD(
        SUM('Sales'[Netamount]), 
        'Sales'[invdate]
    )
)

     

     

    Regards,

    Nono Chen

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