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
littlemojopuppy's avatar
littlemojopuppy
Community Champion
6 years ago

Hi!

I don't mean to sound overly critical but it feels like your entire approach is harder than it should be.

 

I took your PBIX file and made some modifications.  You can download it from here.

  • I added a Calendar table with the following fields: year, month name, month number, weekday, weekday number and most importantly week number, along with a date hierarchy
  • Created a relationship between my Calendar table and your Data table on Date.Date > Data.LeaveDate
  • I created four measures: total transactions, total invoices, total receipts and Invoices - Receipts.  I guessed at what the definition of these should be and I can help refine if you like
  • Laid them out in a matrix with a date slicer.

Using structure like this instead of creating completely separate tables for each week should tremendously simplify what you're trying to do.  Let me know if I can help further.

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

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      6 years ago

      Good morning!

       

      I wouldn't have expected my response to be spot on perfect.

      I changed the relationship between Calendar and Data to work off the Created date instead.  And I added Week Starting/Ending columns to the Calendar table.  These run from Sunday to Saturday...your visualization seems to indicate that your calendar runs Monday to Sunday so feel free to change as appropriate.  You can use those in your measure like this:

       

          SUMX(
        SUMMARIZE(
            Data,
            "Total Debt", 
            SUMX(
                FILTER(
                    Data,
                    Data[Effective] <= RELATED('Calendar'[Week Ending Date]) && 
                    Data[Leave Date] > RELATED('Calendar'[Week Ending Date])
                ),
                Data[Transaction Amount]
            )
        ),
        [Total Debt]
    )

       

      And here's the PBIX.  Hope this helps!