Forum Discussion
Helper IIIYTD for current and Last Year
Hi Waseem , Thank you for reaching out to the Microsoft Community Forum.
Make sure you have a dedicated Date Table with a continuous range of dates that covers your data. If you don’t have one yet, create it. Once created, add useful columns like Year, Month, and MonthName. Go to the Modeling tab -> choose Mark as Date Table and select the Date column. Next, ensure there's an active relationship between the Date column in your fact table and the Date column in this Date Table. You can check and configure this in the Model view.
Example DAX for date table: Date = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))
Make sure any slicers, especially those used to select a time period are built using columns from the Date Table, not from your fact table. If your slicer uses the fact table’s date or just the month name, Power BI will limit the filter context to that exact selection, breaking the expected YTD behaviour.
Now, create your YTD measure using a standard time intelligence function like DATESYTD, which understands the year structure. Example:
Sales_YTD_Advanced =
CALCULATE(
SUM('FactTable'[Value]),
DATESYTD('Date'[Date]),
ALLSELECTED('Date'[Month])
)
This measure should sum up all values from January 1 of the selected year up to the latest date in your selection.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Community SupportHi Waseem , Thank you for reaching out to the Microsoft Community Forum.
Make sure you have a dedicated Date Table with a continuous range of dates that covers your data. If you don’t have one yet, create it. Once created, add useful columns like Year, Month, and MonthName. Go to the Modeling tab -> choose Mark as Date Table and select the Date column. Next, ensure there's an active relationship between the Date column in your fact table and the Date column in this Date Table. You can check and configure this in the Model view.
Example DAX for date table: Date = CALENDAR(DATE(2020,1,1), DATE(2025,12,31))
Make sure any slicers, especially those used to select a time period are built using columns from the Date Table, not from your fact table. If your slicer uses the fact table’s date or just the month name, Power BI will limit the filter context to that exact selection, breaking the expected YTD behaviour.
Now, create your YTD measure using a standard time intelligence function like DATESYTD, which understands the year structure. Example:
Sales_YTD_Advanced =
CALCULATE(
SUM('FactTable'[Value]),
DATESYTD('Date'[Date]),
ALLSELECTED('Date'[Month])
)
This measure should sum up all values from January 1 of the selected year up to the latest date in your selection.
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
WaseemGreatttttttt... Many thanks dear. You relieved me from many days' tension. Thats worked great. All Kudus to you
- Waseem
One more question. In the same file I have last year values (for 2024) for which i created a measure as follows:
LY = CALCULATE([Amount],DATEADD('Date'[Date],-1,YEAR),REMOVEFILTERS('Date'[Date]))the above measuer is working perfectly for th eselected month numbers. However now i intend to calculate YTD for same period last year as well to show them as comparative numbers. (for example when i select Apr 2025, there is a value for YTD Jan-Apr 2025 but I also need another value showing Jan-Apr 2024. How can i use above measure to calculate last year YTD numbers please.Regards