Calculate the MTD and YTD for the corresponding dax

[Anonymous]

I am having a table where there are duplication of column code and its corresponding productioncount . So I wrote the DAX to take average of production of each code and sum it up to get the total production of day. 

CALCULATE(SUMX(VALUES('WASTAGE'[CODE]), AVERAGE('WASTAGE'[PRODUCTION])))

Now I need to calculate MTD and YTD using the same logic i.e, when i select a date, it needs to sum up the total production (calculated with above logic) till the date. 

[123abc]

To calculate MTD (Month-to-Date) and YTD (Year-to-Date) totals based on your existing logic, you can use the DAX functions such as TOTALMTD and TOTALYTD. Here's how you can do it:

For MTD (Month-to-Date), assuming you have a date column named 'Date' in your 'WASTAGE' table, you can use:

MTD_Production =
CALCULATE (
SUMX ( VALUES ( 'WASTAGE'[CODE] ), AVERAGE ( 'WASTAGE'[PRODUCTION] ) ),
DATESMTD ( 'WASTAGE'[Date] )
)

For YTD (Year-to-Date), you can use:

YTD_Production =
CALCULATE (
SUMX ( VALUES ( 'WASTAGE'[CODE] ), AVERAGE ( 'WASTAGE'[PRODUCTION] ) ),
DATESYTD ( 'WASTAGE'[Date] )
)

In these calculations, DATESMTD and DATESYTD are time intelligence functions that filter dates within the current month or year, respectively.

You can create these measures in your Power BI or any other tool where you're working with DAX. These measures will sum up the average production for each code within the specified time frames (MTD or YTD).

Make sure to replace 'Date' with the actual name of your date column in the 'WASTAGE' table. Adjust the column names if necessary to match your actual data model.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Hi @123abc , Thanks for your response. The total production is getting divided by the number of days till the date selected. Any way to overcome it?

[123abc]

If the total production is being divided by the number of days till the selected date, it means that the AVERAGE function is calculating the average production for each code over all the dates up to the selected date. To overcome this and calculate the total production correctly for MTD and YTD, you should calculate the average production for each code separately and then sum them up.

Here's how you can modify the measures to achieve this:

1. Create MTD Production Measure:

MTD_Production =
VAR SelectedDate = MAX('WASTAGE'[Date])
RETURN
CALCULATE(
SUMX(
VALUES('WASTAGE'[CODE]),
CALCULATE(
AVERAGE('WASTAGE'[PRODUCTION]),
DATESMTD('WASTAGE'[Date]),
'WASTAGE'[Date] <= SelectedDate
)
)
)

This measure calculates the MTD production by calculating the average production for each code within the month of the selected date and then summing up these averages.

1. Create YTD Production Measure:

'

YTD_Production =
VAR SelectedDate = MAX('WASTAGE'[Date])
RETURN
CALCULATE(
SUMX(
VALUES('WASTAGE'[CODE]),
CALCULATE(
AVERAGE('WASTAGE'[PRODUCTION]),
DATESYTD('WASTAGE'[Date], "01/01"),
'WASTAGE'[Date] <= SelectedDate
)
)
)

Similarly, this measure calculates the YTD production by calculating the average production for each code within the year of the selected date and then summing up these averages.

By calculating the average production for each code separately within the specified time frame (MTD or YTD) and then summing up these averages, you ensure that the total production is not divided by the number of days till the selected date.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.