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:

 

 

 

 

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!