Forum Discussion
Helper IYTD calculations using Month-Year
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.
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.
romilv1Thanks Anonymous 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?
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.