YTD calculations using Month-Year

[romilv1]

Hello!

I have a fitler that uses Month-Year in below format. We have month-year column in dim.date table

I want to use YTD calculations but that doens't work as it needs date column where Month-Year column is a text column. To overcome this situation I have created calendar table based on advice posted here: https://community.fabric.microsoft.com/t5/Desktop/Calculate-YTD-with-Month-Year/td-p/3171222

I then join the Calendar.Date with the dimDate.Date column (Many to One and Single) and was able to use the Calendar Date as the argument in TotalYTD calcuations using formula as below;

TOTALYTD(SUM(TableName[Amount])+0.00, 'Calendar'[Date])

Using above formula doesn't give me YTD values but instead give me selected month value example for Dec '23 it should be total value of Jan '23 - Dec '23 but it shows Dec'23 values.

I have also used below formula 

Total = sum(TableName[Amount])

Total YTD = calculate([Total],datesytd('Calendar'[Date],"31/12"))

Second formula also generates the same result and doesn't generate YTD. How do I make YTD work if Month-Year is used in filter?

Existing table (refer column 1 & 3)

TestYTDActuals should show below data

361,494

0

0

0

0

152,182

Instead it shows the data exactly as column 3 which is monthly data.

[v-kaiyue-msft]

Hi @romilv1 ,

1. Please try modifying the slicer to use the date column. Consider using the "Date" column from the calendar table instead of using the "Month-Year" text column for the slicer. This slicer can be formatted to display dates in month-year format while still retaining their Date data type. This change ensures that filter context is correctly applied to time intelligence functions.

2. Please try to use FILTER function instead of datesytd function, such as

Measure =
CALCULATE(SUM(financials[Sales]),FILTER(ALL('financials'),'financials'[Date] <= MAX('financials'[Date]) && 'financials'[Date] >= DATE(YEAR(MAX ('financials'[Date])),1,1)))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[romilv1]

Thanks @v-kaiyue-msft  This is similar to the solution what I was looking for. How can we format the slicer to display dates in Month Year format while retaining "Date" date type?

[v-kaiyue-msft · Accepted solution]

Hi @romilv1 ,

You can click the small arrow next to the date field and select hierarchy. But when you create a relationship (many-to-one or one-to-many) between tables with Date , only the dates on one side show the date hierarchy. For more information, please refer to the link: Solved: Date hierarchy not available - Microsoft Fabric Community.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[romilv1]

Hello! @Ashish_Mathur 

I can't share pbi file as it has confidential data and it is connect to Azure Syanpse which would mean it won't open unless it has access.. If there is any other way to share let me know.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.