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