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

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's avatar
amitchandak
Super User
6 years ago

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.