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.

     

     

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

      • 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.