Forum Discussion

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

Need Help

Show and Tell

Tutorial Requests

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.

MTD Sales.pbix66 KB

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:

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()).
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.