Forum Discussion
Super UserYear-to-Date (YTD) Calculation
I’m working with a custom fiscal calendar (non-standard 4-4-5 model) and need help with YTD calculation that dynamically adjusts based on drill-downs in a hierarchy.
Data Structure:
1. SalesData (Fact Table):
Date ProductID RegionID SalesAmount
2024-01-01 101 R1 500
2024-02-15 102 R2 300
2024-03-05 101 R3 700
2. FiscalCalendar (Dimension Table):
FiscalDate FiscalYear FiscalMonth FiscalWeek
2024-01-01 2024 Q1-01 Week1
2024-02-01 2024 Q1-02 Week5
3. HierarchyData (Dimension Table):
ProductID Category SubCategory
101 Electronics Laptops
102 Home Goods Kitchen
Question:
The YTD sales need to adjust based on the irregular fiscal calendar. For example, when drilling down by FiscalMonth, I want YTD sales up to the selected month, but when drilling down by FiscalWeek, YTD should reflect up to the selected week.
Hi,dk_dk .thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.Hello,Kedar_Pande .I am glad to help you.
I strongly endorse the suggestion made by dk_dk: use the ISIncope function to judge the hierarchy in the matrix and control the display of each level separately.
In this case, you don't need to set up table relationships for the data model, and artificially control the output in visual by reversing the order of the hierarchical relationships to judge the computational logic of the MEASURES.I am going to provide another way to think about it, you can also create model relations for the model, and use the model relations as well as the external filtering of visual to control the cumulative output of the hierarchy.
Like this:
The table dax code:FiscalCalendar = VAR StartDate = MIN('SalesData'[Date]) VAR EndDate = MAX('SalesData'[Date]) VAR CalendarTable = CALENDAR(StartDate, EndDate) RETURN ADDCOLUMNS( CalendarTable, "FiscalYear", YEAR([Date]), "FiscalMonth", VAR CurrentMonth = MONTH([Date]) VAR Quarter = CEILING(CurrentMonth / 3.0, 1) VAR MonthInQuarter = CurrentMonth - (Quarter - 1) * 3 RETURN "Q" & Quarter & "-" & FORMAT(MonthInQuarter, "00"), "FiscalWeek", "Week" & WEEKNUM([Date], 1) - WEEKNUM(StartDate, 1) + 1 )Amount Accumulation = VAR _date=MAX('SalesData'[Date]) VAR _weekresult=CALCULATE(SUM('SalesData'[SalesAmount]),FILTER(ALL('SalesData'),'SalesData'[Date]<=_date)) RETURN _weekresultThe need to dynamically adjust year-to-date (YTD) sales has been successfully implemented using the drill-down effect in the matrix. The matrix's drill-down feature automatically adjusts the data displayed according to different hierarchies (e.g., financial month and financial week), so you don't need to write additional DAX code!
If you do not want to use the matrix's hierarchical drill-down effect, you can switch + isincope to dynamically determine the current level, to achieve dynamic modification of the corresponding level of data.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- dk_dk
I know very little about irregular fiscal calendars, so this might not be the right approach, but can't you just create separate "ytd" calculations for each level of the calendar hierarchy (as VARs ) and then use ISINSCOPE to determine which calculation to display?
Hi,dk_dk .thanks for your concern about this issue.
Your answer is excellent!
And I would like to share some additional solutions below.Hello,Kedar_Pande .I am glad to help you.
I strongly endorse the suggestion made by dk_dk: use the ISIncope function to judge the hierarchy in the matrix and control the display of each level separately.
In this case, you don't need to set up table relationships for the data model, and artificially control the output in visual by reversing the order of the hierarchical relationships to judge the computational logic of the MEASURES.I am going to provide another way to think about it, you can also create model relations for the model, and use the model relations as well as the external filtering of visual to control the cumulative output of the hierarchy.
Like this:
The table dax code:FiscalCalendar = VAR StartDate = MIN('SalesData'[Date]) VAR EndDate = MAX('SalesData'[Date]) VAR CalendarTable = CALENDAR(StartDate, EndDate) RETURN ADDCOLUMNS( CalendarTable, "FiscalYear", YEAR([Date]), "FiscalMonth", VAR CurrentMonth = MONTH([Date]) VAR Quarter = CEILING(CurrentMonth / 3.0, 1) VAR MonthInQuarter = CurrentMonth - (Quarter - 1) * 3 RETURN "Q" & Quarter & "-" & FORMAT(MonthInQuarter, "00"), "FiscalWeek", "Week" & WEEKNUM([Date], 1) - WEEKNUM(StartDate, 1) + 1 )Amount Accumulation = VAR _date=MAX('SalesData'[Date]) VAR _weekresult=CALCULATE(SUM('SalesData'[SalesAmount]),FILTER(ALL('SalesData'),'SalesData'[Date]<=_date)) RETURN _weekresultThe need to dynamically adjust year-to-date (YTD) sales has been successfully implemented using the drill-down effect in the matrix. The matrix's drill-down feature automatically adjusts the data displayed according to different hierarchies (e.g., financial month and financial week), so you don't need to write additional DAX code!
If you do not want to use the matrix's hierarchical drill-down effect, you can switch + isincope to dynamically determine the current level, to achieve dynamic modification of the corresponding level of data.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
