Forum Discussion

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

Need to find future date tasks count

Hi amitchandak  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 2...
need help in DAX
  • Anonymous's avatar
    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

Navaneetharaju_'s avatar
Navaneetharaju_
Icon for Helper II rankHelper 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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Navaneetharaju_ ,

Can you please share some more detail information about this issue? They should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

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

    Hi Anonymous , 

     

    Can you please use this below mentioned table.

     

    Task_NameTask_Start_dateTask_start_timerepeat_everyrepeat_byEnd OccranceEnd whenServer
    Task A20-Jan-2410:001days  ProdAM
    Task B20-Jan-2412:002days  ProdAM
    Task C

    20-Jan-24

     

    		15:0015minutes525-Jan-2024

    ProdSM

    Task D

    20-Jan-24

    		18:001days 26-Jan-2024

    ProdAM

    Task E

    20-Jan-24

    		15:001weeks 28-Jan-2024

    ProdSM

     

    If i select only one date in my slicer, for example 26-Jan- 2024, the result should be on that date is 

    Expected Result     
    Result on 26th- Jan-2024     
    Time BinCount      
    10:00 - 11:001      
    12:00 - 13:001      
    15:00 - 16:001Task C count won't come because it stopped on 25-Jan-2024.
    18:00 - 19:001      

     

    future date also has to select like 25-Feb-2024 and 01-Mar-2024(randomly)

     

    Thank you.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      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