Forum Discussion

AgencyPowerBi's avatar
AgencyPowerBi
Helper III
2 years ago
Solved

Calculating subtotal based on multiple time periods

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!

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • AgencyPowerBi's avatar
    AgencyPowerBi
    Helper III
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        AgencyPowerBi
        Helper III
        2 years ago

        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