Forum Discussion

hpatel24779's avatar
hpatel24779
Icon for Helper II rankHelper II
1 year ago

Calculate weeks between step date and calendar

Hi All,   I have a dataset of all cases that are pending and i need to identify if the wait weeks are increasing or decreasing each month. I need a measure that works out the longest wait per month...
Need Help
Show and Tell
Tips and Tricks
rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
1 year ago

Hello hpatel24779 ,

 

Do you want something like this below? Please confirm my understanding.

The below is for a case selected.

 

rajulshah's avatar
rajulshah
Icon for Resident Rockstar rankResident Rockstar
1 year ago

hpatel24779 , is there any update on this?

  • hpatel24779's avatar
    hpatel24779
    Icon for Helper II rankHelper II
    1 year ago

    Hi rajulshah 

     

    apologies for the late reply. Yes ideally that is what i would like provided the logic is correct.

     

    kind regards

     

    Hetal

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    1 year ago

    Hello hpatel24779 ,

     

    I have created a Date table which is related to the fact table and have created a measure as follows:

    Longest wait per month = 
VAR MaxNumberofWeeks = 
    MAXX ( 
            ALLSELECTED ( 'Test file for wait weeks(Test file)'[Step Date] , 'Test file for wait weeks(Test file)'[WORKFLOW_STEP_ID] ),
            DATEDIFF ( [Step Date] , MAX ( Dates[Date] ) , WEEK )
        )
RETURN
    IF ( MaxNumberofWeeks > 0, MaxNumberofWeeks )

     

    Then, this measure has been plot in the chart with Month Year and WorkflowId as a slicer.

     

    Please let me know if this didn't help.