Calculating subtotal based on multiple time periods

[AgencyPowerBi]

Hi,

I have a problem with calculating sum of client spend, based on multiple time periods. So model is like:

1. Data table

Client, Date, Spend amount

2. Client table

Client, Sdate, Edate

3. Date table

So, problem is the Client table. Since Client may repeat (being active in 2020 and again in 2022, for instance) I cannot make an relationship between Client and Data table.

Also I don't know how to filter on start and end date, when there are numerous time periods for the same Client?

I've tried to make a calculated column in Client table, but lack of active relationship between Data and Client table, makes a problem.

Is there a pattern that could solve this problem?

Thanks!

[AgencyPowerBi]

Hi,

so here is the sample model. There is just one Client in 2020. Client entry has defined three periods of activity. 

As you can see XYZ is active in all months, I just want sums for specifically defined periods from Client table.

What kind of relationship I must make and what is the measure/calculated column I must do to make this happen? 

Many thanks!

Sample model 

[Anonymous]

Hi @AgencyPowerBi ,

I think you can try to add a [Flag] column by dax in [Date table]

Flag = 
VAR _Virtual_Table = ADDCOLUMNS(Client,"Flag",IF('Date table'[Date] >= [Sdate] && 'Date table'[Date]<= [Edate],1,0))
RETURN
SUMX(_Virtual_Table,[Flag])

Then add this calculated column in your matrix and set it to show items when value =1.

Best Regards,
Rico Zhou

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

[AgencyPowerBi]

Thanks for this solution.. 

It works only in example of one client 😕

I cannot scale it to multiple clients, because adding additional row in the same or overlaping period increases number in [Flag] from 1 to 2,3,4...

Ultimately, I would like to summarize per Client when Client in the time he was active.

My problem is how to make consecutive time periods that would fit into :

date >=start date && date <=end date

[Anonymous · Accepted solution]

Hi @AgencyPowerBi ,

My workaround is based on your sample. Here I add a new client in your sample. I think it could work.

Please share a new sample with multiple clients with us, and show us a screenshot with the result you want.

This will make us easier to help you.

Best Regards,
Rico Zhou

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

[AgencyPowerBi]

Sample model 

Ok, here is a new model.

I've calculated expected values by filtering in Excel, and pasted the table in the model. 

Hopefully now it will be a bit easier to get to the solution.

I made two of the periods for both clients equal, and some are overlapping.

Thanks for all the help!

[Anonymous]

Hi @AgencyPowerBi ,

According to your statement, I think you want to calculate the spend amounts.

Your issue is based on your data model. I am confused that whether there will be duplicate clients in Data table.

Please share a sample file with us as show a screenshot with the result you want. This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

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