Need to find future date tasks count

I want to create a measure to find selected date task count,

Task_Name	Task_Start_date	Task_start_time	repeat_every	repeat_by	End Occrance	End when	Server
Task A	20-Jan-24	10:00	1	days			ProdAM
Task B	20-Jan-24	12:00	2	days			ProdAM
Task C	20-Jan-24	15:00	15	minutes	5	25-Jan-24	ProdSM

Date slicer -

I want to select a one future data in my slicer, In that time_bin it should display how many tasks will come on that selected date

Slicer - date slicer - 22-Jan-2024

Result should be on 22-Jan-2024

Task A and Task B will come and Task C will run 4 times then it will end

Task_start_time	Count
10:00	1
12:00	1
15:00	4

Server	Count
ProdAM	2
ProdSM	4

Result should be on 23-Jan-2024

(Task B won't come in this day)

Task_start_time	Count
10:00	1
12:00	0
15:00	4

Server	Count
ProdAM	1
ProdSM	4

Result shoudl be on 26-Jan-2024

After the end_date Task C won't come

Task_start_time	Count
10:00	1
12:00	1

Server	Count
ProdAM	2

I want the measure to create the count like above in tasks and servers

need help in DAX

Anonymous

2 years ago

HI Navaneetharaju_,

I'd like to suggest create unconnected table a source of slicer. You can try to use the following measure formula if it suitable for your requirement:

formula =
VAR selected =
    MAX ( NewTable[Date] )
VAR timeStart =
    MAX ( Table1[Task_Start_date] )
RETURN
    CALCULATE (
        COUNT ( Table1[Task_Name] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            AND (
                [Task_Start_date] <= selected,
                OR ( [End when] = BLANK (), [End when] >= selected )
            )
                && [Task_start_time] >= timeStart
        )
    )

Regards,

Xiaoxin Sheng

6 Replies

123abc
Community Champion
2 years ago
To achieve this, you can create two measures - one for task count and another for server count. The measures should take into account the selected date, the start date, and the repetition logic. Below is an example of how you can create these measures:

Task Count =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Task_Start_date] <= SELECTEDVALUE('Date Slicer'[Date]) &&
(
'YourTable'[End Occrance] >= SELECTEDVALUE('Date Slicer'[Date]) ||
ISBLANK('YourTable'[End Occrance])
) &&
(
'YourTable'[Task_start_time] <= SELECTEDVALUE('Date Slicer'[Date]) ||
ISBLANK('YourTable'[Task_start_time])
) &&
(
DATEDIFF(
SELECTEDVALUE('Date Slicer'[Date]),
'YourTable'[Task_Start_date],
DAY
) % 'YourTable'[repeat_every] = 0 ||
ISBLANK('YourTable'[repeat_every])
)
)
)

Server Count Measure:

Server Count =
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Task_Start_date] <= SELECTEDVALUE('Date Slicer'[Date]) &&
(
'YourTable'[End Occrance] >= SELECTEDVALUE('Date Slicer'[Date]) ||
ISBLANK('YourTable'[End Occrance])
) &&
(
'YourTable'[Task_start_time] <= SELECTEDVALUE('Date Slicer'[Date]) ||
ISBLANK('YourTable'[Task_start_time])
) &&
(
DATEDIFF(
SELECTEDVALUE('Date Slicer'[Date]),
'YourTable'[Task_Start_date],
DAY
) % 'YourTable'[repeat_every] = 0 ||
ISBLANK('YourTable'[repeat_every])
)
)
)

Make sure to replace 'YourTable', 'Date Slicer', and other column names with the actual names used in your Power BI model. These measures should give you the task count and server count based on the selected date in the slicer.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
- Navaneetharaju_
  Helper II
  2 years ago
  Hi 123abc ,
  
  I have tried that the measure u shared. I haven't received any results.
  
  I have attached the screenshots
  
  Please help me to get this done. if we can connect via google meet also. i'm fine with that.
  - 123abc
    Community Champion
    2 years ago
    Let's modify the measure to better suit your needs. In this case, we will create two measures: one for Task_start_time count and another for Server count.
    
    TaskStartTimeCount =
    VAR SelectedDate = SELECTEDVALUE('Date Slicer'[Date])
    RETURN
    CALCULATE (
    COUNTROWS ( 'YourTable' ),
    FILTER (
    ALL('YourTable'),
    'YourTable'[Task_Start_date] <= SelectedDate
    && (
    'YourTable'[End Occurance] >= SelectedDate
    || ISBLANK ( 'YourTable'[End Occurance] )
    )
    && (
    'YourTable'[repeat_every] = 0
    || (
    'YourTable'[repeat_every] > 0
    && MOD ( SelectedDate - 'YourTable'[Task_Start_date], 'YourTable'[repeat_every] ) = 0
    )
    )
    )
    )
    ServerCount =
    VAR SelectedDate = SELECTEDVALUE('Date Slicer'[Date])
    RETURN
    CALCULATE (
    COUNTROWS ( 'YourTable' ),
    FILTER (
    ALL('YourTable'),
    'YourTable'[Task_Start_date] <= SelectedDate
    && (
    'YourTable'[End Occurance] >= SelectedDate
    || ISBLANK ( 'YourTable'[End Occurance] )
    )
    && (
    'YourTable'[repeat_every] = 0
    || (
    'YourTable'[repeat_every] > 0
    && MOD ( SelectedDate - 'YourTable'[Task_Start_date], 'YourTable'[repeat_every] ) = 0
    )
    )
    )
    )
    * DISTINCTCOUNT('YourTable'[Server])
    
    Replace 'YourTable' with the actual name of your table.
    These measures should provide you with the count for each Task_start_time and Server based on the selected date in the slicer. If you're still facing issues, please double-check your data and ensure that the date values in your table are formatted correctly.
    
    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    
    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.