Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI : Complex Calculations on Calculated Table

 Hi, I am working on a scenario where an Application/Case is filed into a system and its journey is also tracked as the application/case progresses to further stages. I need to find how much time an ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous 

    You can create a column in the Application History table. 

    Time(days) = IF([Prev] = "Stage 1",
DATEDIFF(RELATED('Application Master'[Created On]),'Application History'[updated on],day), 
DATEDIFF(CALCULATE(MAX([Updated On]),FILTER('Application History',[Updated On]<EARLIER([Updated On]))),[Updated On],DAY))

     

    If need a calculated table, you can summarize():

    Table = SUMMARIZE('Application History','Application History'[Application ID],[Prev],'Application History'[Time(days)])


    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous 

You can create a column in the Application History table. 

Time(days) = IF([Prev] = "Stage 1",
DATEDIFF(RELATED('Application Master'[Created On]),'Application History'[updated on],day), 
DATEDIFF(CALCULATE(MAX([Updated On]),FILTER('Application History',[Updated On]<EARLIER([Updated On]))),[Updated On],DAY))

 

If need a calculated table, you can summarize():

Table = SUMMARIZE('Application History','Application History'[Application ID],[Prev],'Application History'[Time(days)])


Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous thank you for your DAX, it is working in majority cases, for rest I have some data anomalies. Can you please help me tweak your DAX to find time difference for below scenarios.

    For each Application ID, find TAT between Stage 1 to Stage 4, Stage 2 to Stage 4 and Stage 3 to Stage 4.

     

    Here is what I am trying :

    Stage 1 to Stage 4 = DATEDIFF(RELATED('Application Master'[Created On]), CALCULATE(MAX(Application History[Updated On]), FILTER(Application History, Application History[New] = "Stage 4")),DAY).
     
    But it gives me overall Max of [Updated On] for "Stage 4". I need max(Updated On) of "Stage 4" for that particular Application ID. Can you please help what additional Filter is required ?