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

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'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!