Calculate outflow which not returns within 6 month

[powerbizoeker]

Hello,

I'm relative new to PowerBi and i'm searching to a way to calculate outlow's which are not flowing in within 6 months.

I have 4 tables: inflow, current flow, outflow and calender.

The inflow table has clients which are flowing in a treatment on reference date.

The current flow table has clients which are current in a treatment on reference date.

The outflow table had clients which are flowing out of their treatment on reference date.

Reference date is per month.

Example with dummy data:

Inflow table

Client	Start_date	Stop_date	Reference_date
A	5-6-2019		6-2019
B	12-8-2019		8-2019
A (return)	9-3-2020		3-2020
C	14-3-2020		3-2020
D	5-5-2020		5-2020
C (return)	10-6-2020		6-2020

Current flow table

Client	Start_date	Stop_date	Reference_date
B	12-8-2019		6-2020
D	5-5-2020		6-2020
A (return)	9-3-2020		6-2020
C (return)	10-6-2020		6-2020

Outflow table

Client	Start_date	Stop_date	Reference_date
A	5-6-2019	2-12-2020	9-2019
C	14-3-2020	2-5-2020	5-2020

I want to calculate the outflow per month, which is not returned (flowedin) within 6 month.

So on reference_date 06-2020 the count is 1, because client A has flowed out for 6 month and did not return within 6 months

Client C will not show up on reference_date 11-2020, which is 6 months after client C is flowed out. This because he return within 6 months.

Where do i start?

[Icey]

Hi @mahoneypat ,

---

Outflow table

Client	Start_date	Stop_date	Reference_date
A	5-6-2019	2-12-2020	9-2019
C	14-3-2020	2-5-2020	5-2020

---

For Client A, the Stop_date is 2-12-2020, how did you get it?

---

Client C will not show up on reference_date 11-2020, which is 6 months after client C is flowed out. This because he return within 6 months.

---

Why are you sure that Clinet C will not show up on reference_date 11-2020?

And what is the result you want?

Please share us more details. 

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You could write some DAX to work with the way you've structured the data, but I would encourage you to start with a different model.  Your columns are the same in all three tables.  I would do the following:

• Disable load on all three queries
• Add a custom column called "Flow Type" to each the type of flow ("Custom", "Inflow", "Outflow")
• Append the 3 queries and load that new one, relate it to your Date table
• Write DAX expressions that leverage the new column to filter on COUNTROWS for the different flow types in the same Date and Client context
• If you go that way and get stuck on the measures, you can post a new example table with the question (I didn't have time to do the above right now, or would have replied with the M code)

I believe this will simplify things for you.  Simple model, simple DAX.  You can then use a shared column for Client, etc. in your visuals and other analyses, instead of worrying about more relationships, TREATAS(), etc.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[parry2k]

@powerbizoeker I would recommend to put sample data in excel sheet for all the tables along with expected output and share that file here thru one drive/google drive, it will help to provide the solution.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[powerbizoeker]

@parry2kThans you for you're reponse. Willing the datasets container clientinformation i can't share the original sets. I have added an example based on dummy data.