MTD calculation

[rajasekar_o]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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]

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

[123abc]

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.

[rajasekar_o]

am using the year filter and month filter  only