Need Support in Complex Dax and Visualization filters

[Navaneetharaju_]

Hi Folks, 

@amitchandak , @MFelix , @Anonymous , @Anonymous, @lbendlin,@Ahmedx,@Ritaf1983, @Fowmy , @VijayP 

I want to create a bar chart, it should show what are the tasks going to run on the selected future date. 

I have a data like this:-

Task Table- 

Task_name, Start_date, Start_time, repeat_on, repeat_every, repeat_by

Repeat_by 

Days

Months

Weeks

Year

Repeat_every

1

2

5

10

Repeat_by field that indicates, how frequent the tasks are going to run on daily basis, two days once, weekly basis etc.

Scenario 1 :

Based on above mentioned data, 1st row task(scheduled_omnichannel_BG_Prod_Daily), that task started running on 03/02/2023, from that date onwards it will run daily, if i select 03/03/2023 in my date slicer , this task count should show on 13:00-14:00 time bin.

Scenario 2 :

9thw row task (FORECAST_S_Every_TwiceOnce_SIT), that task started running on 04/18/2023, from that date onwards it running two days once, if i select 04/19/2023 in my date slicer, this task count shouldn't come on that 10:00-11:00 bin.

Scenario 3:

11th row task(PAP_Autofill_Forecast Task), that task started running on 07/20/2023, from that date onwards its running 7 days once, if i select 07/21/2023 in my filter , this task shouldn't come on that 11:00-12:00 time bin. if i select 07/27/2023 in my date slicer, this task should come on that date and the respective time bin.

Like this way , i have a repeat by -weeks, months, year. some tasks running only once in a month, once in a year .

those task should come only on that date.

Please advice, how proceed this and how acheive these scenarios and plot the tasks count in respective time bin based on date.

[amustafa]

Try creating few calculated columns and measures first.

Next Occurrence =
SWITCH(
TRUE(),
'Table'[repeat_by] = "days", DATEADD('Table'[Start_date], 'Table'[repeat_every], DAY),
'Table'[repeat_by] = "weeks", DATEADD('Table'[Start_date], 'Table'[repeat_every] * 7, DAY),
// Add logic for months and years
'Table'[Start_date] // Default case to return start date if no repeat
)

Time Bin =
SWITCH(
TRUE(),
'Table'[Start_time] >= TIME(13, 0, 0) && 'Table'[Start_time] < TIME(14, 0, 0), "13:00-14:00",
// ... other bins ...
"Outside of bins" // Default case
)

Measure for Filtering Tasks:

Task Count for Selected Date =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Next Occurrence] = SELECTEDVALUE(DateTable[Date])
)
)

Use Visuals to Display Data

• Use a slicer for the date selection.
• Use a matrix or bar chart to show the number of tasks per time bin.
• You can use the created measure to filter the visual to only show data for the selected date.

Hope this helps. Make sure to adjust table and column names accordingly