Forum Discussion

Navaneetharaju_'s avatar
Navaneetharaju_
Icon for Helper II rankHelper II
2 years ago

Need Support in Complex Dax and Visualization filters

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.

Need Help

1 Reply

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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