Forum Discussion
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 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?
TIA
11 Replies
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.
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 , 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
I remember I downloaded, initial file. If you can give me sample output, I will try
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/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).
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.
