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