Forum Discussion

4-3-3's avatar
4-3-3
Helper I
6 years ago

Help with virtual table/advanced summarize?

Hey team, I facing the following scenario. I have financial data of receipts and invoices for our customers and I need to figure out the amount of 'active debtors' at the end of each week.   I am a...
Need Help
4-3-3's avatar
4-3-3
Helper I
6 years ago

Hey, thanks heaps for trying but unfortunately this is not giving the desired outcome.

 

I agree with the fact that creating a weekly table is not efficient but that was there to demonstrate what I am after and currently only able to achieve when creating the weeklye tables. But what I started with almost got me where I was heading although I need to be able to do without creating all the week table with below measure.

 

 

SUMMARIZE(Data,Data[Customer ID], "Total Debt", CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= {weekEnding}  && Data[Leave Date] > {weekEnding})))

 

 

Your measures only look at the leave date, whereas I need to look at both the leave date and the effective date of the transaction to make sure that both will get returned when this applies.

 

This is the first summarize to get the rolled up transactions for each Customer ID for that particular snapshot (weekEnding), then with the second SUM I only need add values higher than 0. Then I need to be able to overlay this data over the Week Calendar so I get this:

 

 

 

 

  • 4-3-3's avatar
    4-3-3
    Helper I
    6 years ago

    Sorry, I responded to your first post first without reading this one. I have a join between the tables in the file that I just posted.

     

    Although I just quickly removed the join and I am still not getting the correct amount. It is giving 810.15 now..