Languages Advisor | DAX

Time Categories and Period Analysis

Learn to categorize timestamps into shifts and compute measures for today, current week, and month in Power BI using DAX calculated columns and time-intelligence functions. Enhance data visualization and analysis with interactive visuals and


Empty image or helper icon

Prompt

Hi all,

i have a datasets with several records including timestamp. The requirement is to have a filter to be able to select

1). Current Shift (6:00-14:00, 14:00-22:00, 22:00-6:00)

2). Today Overview

3). Current Week

4). Current Month

With possibility to see previous period for some visuals. What is the best approach? I have tried to use Field Parameters with calcualtions Current and Previous Periods. But the issue is to do proper Switch for the previous week and shift.

Structure of the input json:

{“timestamp”:“2024-03-06T10:40:36”,“resource_name”:“dmg_ctx_beta_1250_tc_1”,“datamodel_version”:“general_v_1_0_0”,“resource”:“101”,“active_program_current_state_number”:“0”,“active_program_last_transition_number”:“2”,“active_program_name”:“_N_CH12_ROBOTER_SPF”,“errorcode”:“0”,“message”:“”,“production_desired_parts”:“410”,“production_part_counter”:“”}
{“timestamp”:“2024-03-06T10:40:37”,“resource_name”:“dmg_ctx_beta_1250_tc_1”,“datamodel_version”:“general_v_1_0_0”,“resource”:“101”,“active_program_current_state_number”:“1”,“active_program_last_transition_number”:“0”,“active_program_name”:“_N_CM100GE000010_MPF”,“errorcode”:“0”,“message”:“”,“production_desired_parts”:“410”,“production_part_counter”:“”}

Answer

In your scenario, you have a dataset which captures timestamps in a field. You are looking to assign these timestamps to certain categories such as shift times (like 6:00-14:00, 14:00-22:00, 22:00-6:00), today's data, current week data and current month data.

Time Shift Calculation

To create your "Shift" category, you need to extract the time from the timestamp and decide its shift. This can be accomplished using a calculated column and DAX's SWITCH function. First, extract the hour of the timestamp using HOUR() function. Then, categorize it based on three ranges (6 to 13, 14 to 21 and 22 to 5).

Shift =
SWITCH (
    TRUE,
    HOUR( YourTable[timestamp] ) >= 6 && HOUR( YourTable[timestamp] ) < 14, "6:00-14:00",
    HOUR( YourTable[timestamp] ) >= 14 && HOUR( YourTable[timestamp] ) < 22, "14:00-22:00",
    "22:00-6:00"
)

Please remember DAX measures will be calculated on the fly, as per your visualization, whereas DAX calculated columns will be pre-computed and stored in the model, affecting your model's size.

Periods

To see current and previous data for day, week, and month, create some time intelligence computed measures. Please ensure the "timestamp" column is categorized as DateTime in model.

Today's Measures =
CALCULATE(
    MEASURE,
    FILTER(
        ALLSELECTED(YourTable[timestamp]), 
        YourTable[timestamp] > TODAY() - 1 && YourTable[timestamp] <= TODAY() 
    )
)

Previous Day's Measures =
CALCULATE(
    MEASURE,
    FILTER(
        ALLSELECTED(YourTable[timestamp]), 
        YourTable[timestamp] > TODAY() - 2 && YourTable[timestamp] <= TODAY() - 1
    )
)  

Current Week's Measures =
CALCULATE(
    MEASURE,
    FILTER(
        ALLSELECTED(YourTable[timestamp]), 
        WEEKNUM(YourTable[timestamp]) = WEEKNUM(TODAY()) && YEAR(YourTable[timestamp]) = YEAR(TODAY())
    )
)

# Similarly provide formulas for Last Week's Measures, Current Month's Measures, and Last Month's Measurement. 

Replace MEASURE with the actual measure you want to compute, like COUNT, SUM, or ANY other.

Using the above approach, you can create interactive visuals to display current and previous data based on the time frame selected. Moreover, the "Shift" category ensures you can further break down the data by the shift time.

For further deep dive, refer to courses focused on DAX context transitions, time-intelligence functions, and best practices over at the Enterprise DNA platform.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

Learn to categorize timestamps into shifts and compute measures for today, current week, and month in Power BI using DAX calculated columns and time-intelligence functions. Enhance data visualization and analysis with interactive visuals and context transitions.