Help with virtual table/advanced summarize?

[4-3-3]

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 able to summarize the data when manually set the date informations (the week ending date) but I need to find a way how to be able to use this in one measure against the week table.

I have attached the example data with last three weeks created manually with summarize. Can you please help me to have this all done with one measure only?

Test Table.pbix 

TIA

[amitchandak]

@4-3-3 

You have to create a date table and have week start and weekend there. Please find the example file.

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Then you can have a formula like this

debt = CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= max(Date[Date])  && Data[Leave Date] > max(Date[Date])))

debt customer =
sumx(SUMMARIZE(Data,Data[Customer ID], "Total Debt",[_debt] ),[_debt])

This kind of daily debtor, depending on the date you choose it will change if you choose week end visual  it will become week debtor

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[4-3-3]

Hi Amit,

thanks for trying to help, much appreciated. I tried to use the two measures you suggeste but not getting the correct outcome either. 

Normally I do include Calendar table in my files as you cant do much without these. I have used 'virtual tables' before where I needed to have a separate 'Month calendar' to get historical data grouped up as needed. I am just stuck with the calculation here as I dont know how to change the 'month calendar' into a week calendar and then I am unsure how to iterate correctly through this data.

Here is the file with the changes you suggested but still not getting the correct outcome. The debt for 15/03 should be 1566.90 (page 1), using your method, I am getting -220.40 (page 2).

PBIX file 

[amitchandak]

On page one it is >0 filter, i check it

All the formula is comment should also work after >0 change

debtAmit = 
var _min = Minx('Calendar','Calendar'[Date])
return
// CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= max(Calendar[Date])  && Data[Leave Date] > max(Calendar[Date])),CROSSFILTER('Calendar'[Date],Data[Effective],None))
 //CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= _min  && Data[Leave Date] >_min),CROSSFILTER('Calendar'[Date],Data[Effective],None))
sumx(SUMMARIZE(CALCULATETABLE(Data,filter(all(Data),Data[Effective] <= _min  && Data[Leave Date] >_min)),Data[Customer ID],"_sum",sum(Data[Transaction Amount])),if([_sum]>0,[_sum],0))
//CALCULATE(sumx(SUMMARIZE(all(Data),Data[Customer ID], "Total Debt", CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= DATE(2020,03,15)  && Data[Leave Date] > DATE(2020,03,15)))),[Total Debt]),CROSSFILTER('Calendar'[Date],Data[Effective],None))

As of now only one uncommented and I merged into on measure

Appreciate your Kudos.

[4-3-3]

Wicked Amit. Almost there! Would you be able to adjust it so it shows the amount per customer, and only the values higher than 0? Like in this screenshot?

https://i.imgur.com/3ZDYYsk.png

[v-kelly-msft]

Hi @4-3-3 ，

Your measure needs to be modified as below:

debtAmit = CALCULATE(SUM(Data[Transaction Amount]), FILTER(Data, Data[Effective] <= MAX('Week Calendar'[Week End])  && Data[Leave Date] >MAX('Week Calendar'[Week End])))

Then create a measure:

Measure = SUMX(DISTINCT('Data'[Customer ID]),CALCULATE(IF([debtAmit]<=0,BLANK(),[debtAmit])))

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[4-3-3]

Hi Kelly,

thanks for trying to help. Unfortunately this is not quite the solution I am after. What I am after is to get the 'debtAmit' and 'Measure' to match the same value (1566.90). The 'debtAmit' measure in your attempt is still listing all transactions, even the ones <0 and that is why it is only showing 810.15

Can you please have a look at it again?

[littlemojopuppy]

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]

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]

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!

[amitchandak]

@4-3-3 , Make sure there is no join between week date and leave date/effective for this formula, or you have used crossfilter to remove that

Refer

: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

I remember I downloaded, initial file. If you can give me sample output, I will try

[4-3-3]

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