MTD Total

[Stelian]

Hi everyone,

I have a table with:

Column 1 - Date,
Column 2 - MTD sales for the current month (e.g., Jan 2024 until the previous day, for example, 30.01.2024),

and Column 3 - MTD sales for the same month last year (e.g., Jan 2023 until the previous day last year, for example, 30.01.2023). The MTD measures are:

MTD CY = CALCULATE(SUM('Actual Sales'[SoldAmount]), DATESMTD(DeliveryNoteDateDim[DeliveryNote Date]))

MTD LY = TOTALMTD(SUM('Actual Sales'[SoldAmount]), DATEADD(DATESMTD(DeliveryNoteDateDim[DeliveryNote Date]), -1, YEAR)) but I would like the columns to bring values until the previous day for CY and LY(30.01.2023 and 2024), and for the total to show the value of the same day for CY and LY (30.01.2023 and 2024).

Can you please tell me how to update the measures?

Thank you,

Stelian

[Stelian]

Thank you for your answer, I've tried your PY formula but it show me the cumulated total to each date (I have days on rows). I need the MTD PY for each day and in Total field the MTD PY for previous day which is the last day with values.

[Anonymous]

Hi @Stelian ，

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[Anonymous]

Hi @Stelian ,

Please have a try.

For the current year MTD (up to the previous day):

MTD CY =
CALCULATE(
SUM('Actual Sales'[SoldAmount]),
DATESBETWEEN(
DeliveryNoteDateDim[DeliveryNote Date],
STARTOFMONTH(DeliveryNoteDateDim[DeliveryNote Date]),
PREVIOUSDAY(MAX(DeliveryNoteDateDim[DeliveryNote Date]))
)
)

For the last year MTD (up to the previous day of the same month):

MTD LY =
CALCULATE(
SUM('Actual Sales'[SoldAmount]),
DATESBETWEEN(
DeliveryNoteDateDim[DeliveryNote Date],
STARTOFMONTH(SAMEPERIODLASTYEAR(DeliveryNoteDateDim[DeliveryNote Date])),
PREVIOUSDAY(ENDOFMONTH(SAMEPERIODLASTYEAR(DeliveryNoteDateDim[DeliveryNote Date])))
)
)

In these measures, the function is used to specify the range of dates for the MTD calculation. The function helps to exclude the current day's sales from the total, ensuring that the calculation is up to the previous day.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...