Forum Discussion
Getting Going with Time Intelligence
I have a file of leave data that has sickness leave data for approximately 2.4 years. I have defined various measures such as total staff, sickness rate and days lost, I need to show the summary metrics for each year (financial plus calendar), in order to compare these measures with government statistics.
I have 3 main tables, staff, sickleave and a date table. The date table is very basic. The tables are connected to each other.
What do I need to do in order to start using the time intellgence features, just create measures that use the trime intelligence functions?
Walt1010 You need to mark your table as a date table for the DAX TI functions to work "reliably". However, I would highly recommend using offsets instead in your date table. Also, have a look at the following. You may find this helpful - https://medium.com/@gdeckler/to-bleep-with-time-intelligence-0b0c2a4708d9
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Hi Walt1010
If you want to improve your date table for future projects, here's the calendar I always use. Replace the MIN/MAX with dates from your primary data table for dynamic start and end dates
Date = ADDCOLUMNS ( CALENDAR ( MIN ( [Date column from existing table] ), MAX ( [Date column from existing table] ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "MonthYear", FORMAT ( [Date], "MMMM YYYY" ), "MonthYearShort", FORMAT ( [Date], "MMM YY" ), "MonthYearNo", FORMAT ( [Date], "YYYYMM" ), "Quarter", QUARTER ( [Date] ), "Year", YEAR ( [Date] ), "Day", DAY ( [Date] ), "WeekNumber", WEEKNUM ( [Date] ), "WeekdayNum", WEEKDAY ( [Date] ), "WeekdayName", FORMAT ( [Date], "DDDD" ), "PreviousWeek", WEEKNUM ( [Date] ) -1 , "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1), "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7), "YearMonth", FORMAT ( [Date], "YYYY-M" ), "Financial Year", IF (MONTH ([Date]) >= 1 && MONTH ([Date]) <= 12, YEAR ([Date]), YEAR ([Date]) + 1) )Once marked as the date table, you can add a variety of measures, some examples below
-- Total Days Lost This Year (Calendar) DaysLost_CY := CALCULATE([Days Lost], DATESYTD('Date'[Date])) -- Total Days Lost This Financial Year DaysLost_FY := CALCULATE([Days Lost], DATESYTD('Date'[Date], "06/30")) -- Sickness Rate Last Year SicknessRate_LY := CALCULATE([Sickness Rate], SAMEPERIODLASTYEAR('Date'[Date]))Hope this helps, please give a thumbs up and mark as solved if it does, thanks!
5 Replies
Walt1010 You need to mark your table as a date table for the DAX TI functions to work "reliably". However, I would highly recommend using offsets instead in your date table. Also, have a look at the following. You may find this helpful - https://medium.com/@gdeckler/to-bleep-with-time-intelligence-0b0c2a4708d9
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008Hi Walt1010
Greg_Deckler Thanks for the inputs.I hope the information provided by user was helpful. If you still have questions, please don't hesitate to reach out to the community. if issue resolved please accept it as solution.
Hi Walt1010
I wanted to check if you had the opportunity to review the information provided by user. Please feel free to contact us if you have any further questions.
Hi Walt1010
If you want to improve your date table for future projects, here's the calendar I always use. Replace the MIN/MAX with dates from your primary data table for dynamic start and end dates
Date = ADDCOLUMNS ( CALENDAR ( MIN ( [Date column from existing table] ), MAX ( [Date column from existing table] ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "MonthYear", FORMAT ( [Date], "MMMM YYYY" ), "MonthYearShort", FORMAT ( [Date], "MMM YY" ), "MonthYearNo", FORMAT ( [Date], "YYYYMM" ), "Quarter", QUARTER ( [Date] ), "Year", YEAR ( [Date] ), "Day", DAY ( [Date] ), "WeekNumber", WEEKNUM ( [Date] ), "WeekdayNum", WEEKDAY ( [Date] ), "WeekdayName", FORMAT ( [Date], "DDDD" ), "PreviousWeek", WEEKNUM ( [Date] ) -1 , "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1), "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7), "YearMonth", FORMAT ( [Date], "YYYY-M" ), "Financial Year", IF (MONTH ([Date]) >= 1 && MONTH ([Date]) <= 12, YEAR ([Date]), YEAR ([Date]) + 1) )Once marked as the date table, you can add a variety of measures, some examples below
-- Total Days Lost This Year (Calendar) DaysLost_CY := CALCULATE([Days Lost], DATESYTD('Date'[Date])) -- Total Days Lost This Financial Year DaysLost_FY := CALCULATE([Days Lost], DATESYTD('Date'[Date], "06/30")) -- Sickness Rate Last Year SicknessRate_LY := CALCULATE([Sickness Rate], SAMEPERIODLASTYEAR('Date'[Date]))Hope this helps, please give a thumbs up and mark as solved if it does, thanks!
Hi Walt1010
Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted. if you have any other issues please reach community.
