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.

  • 4-3-3's avatar
    4-3-3
    Helper I
    6 years ago

    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's avatar
      v-kelly-msft
      Community Support
      6 years ago

      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's avatar
        4-3-3
        Helper I
        6 years ago

        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?