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
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.