Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate the Daily Paying Change

How to show daily “paying” change based on the columns below? I tried to do a Dax query using the function DATEADD() but it is throwing an error since there are multiple same dates with different data. Not exactly sure how to go from here. 

 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    If I understand you correctly, you could use below formula instead.

    Column = 
var day_1 = CALCULATE(MAX('Table'[date]),FILTER('Table','Table'[date]<EARLIER('Table'[date])))
var paying_1 = CALCULATE(SUM('Table'[paying]),FILTER(ALLEXCEPT('Table','Table'[accountname]),'Table'[date]=day_1))
return
'Table'[paying]-paying_1

     Result would be shown as below.

    If I misunderstood your meaning, please show some sample data and expected result to us.

     

    Best Regards,

    Jay

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Not very clear. But with help  from date table try measures like

     

    This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
    Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
    Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

     

    diff =[This Day] - [Last day]

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for your help but I get the following error: 

      I guess my question is how to find the day to day change without running into this exception. Would something need to be done with the signupdate? Maybe group them? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    If I understand you correctly, you could use below formula instead.

    Column = 
var day_1 = CALCULATE(MAX('Table'[date]),FILTER('Table','Table'[date]<EARLIER('Table'[date])))
var paying_1 = CALCULATE(SUM('Table'[paying]),FILTER(ALLEXCEPT('Table','Table'[accountname]),'Table'[date]=day_1))
return
'Table'[paying]-paying_1

     Result would be shown as below.

    If I misunderstood your meaning, please show some sample data and expected result to us.

     

    Best Regards,

    Jay