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, Sdat...
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.

     

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

     

     

    • AgencyPowerBi's avatar
      AgencyPowerBi
      Helper III
      2 years ago

      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!