Getting Going with Time Intelligence

[Walt1010]

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?

[Greg_Deckler · Accepted solution]

@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-TIT...

[v-priyankata]

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.

[wardy912 · Accepted solution]

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!

[v-priyankata]

Hi @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.

[v-priyankata]

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.

[Greg_Deckler · Accepted solution]

@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-TIT...