Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 application/case spends in each stage bucket. Below are my master and transaction table : 

 

Input.PNG

 

Below is the expected output : 

 

Output.PNG 

 

Can someone help me how can I create above calculated table which can help me govern how much time is spent in each bucket.
@GilbertQ 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@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))

application history.JPG

 

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.

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@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))

application history.JPG

 

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
Not applicable

Hi @V-pazhen-msft 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 ?
amitchandak
Super User
Super User

@Anonymous , a new column in Application history

 

New column = datediff(maxx(filter(Applicationmaster ,Applicationmaster[Application ID] =Applicationhistory[Application ID]),Applicationmaster[Created on]),Applicationhistory[updated on],day)

 

New column = datediff(maxx(filter(Applicationmaster ,Applicationmaster[Application ID] =Applicationhistory[Application ID]),Applicationmaster[Created on]),Applicationhistory[updated on],day) & "Day"

Anonymous
Not applicable

Thanks a lot @amitchandak for your quick answer. Your DAX also gives me datediff from stage 1 to stage 2, stage 1 to stage 3 and stage 1 to stage 4. Apologies couldn't reply earlier as I was unavailable.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.