Forum Discussion

trotsale's avatar
trotsale
Regular Visitor
9 years ago
Solved

Calculating difference between rows by date

Hello!   I have a problem and here it is:   In my Power Bi I have a table with report which is getting data from local folder. A key is date, which is adding as a column on every refresh. It look...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi trotsale

     

    Try out this method. This method grabs the device ids from last month and subtracts them from device ids this month.

     

    DevicesByDate difference last month compared to this month = CALCULATE(COUNTROWS('Test');DISTINCT('Test'[Date]);DATEADD(DATESBETWEEN(Test[Date];STARTOFMONTH(Test[Date]);ENDOFMONTH(Test[Date]));-1;MONTH)) - CALCULATE(COUNTROWS('Test');DISTINCT('Test'[Date]);DATESINPERIOD(Test[Date];LASTDATE(Test[Date]);-1;MONTH)) 

     

    Best,

    Martin

GilbertQ's avatar
GilbertQ
Super User
9 years ago
Off the top of my head have you tried another calculation which would use the PREVIOUSDAY DAX function?

Devices Previous Day = CALCULATE([Devices],PREVIOUSDAY('Date Table'[Date]))

You would need to have a Date table in order for this to work. I think it might look for the Previous Day being 08/14/2017 though.
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi trotsale

     

    Try out this method. This method grabs the device ids from last month and subtracts them from device ids this month.

     

    DevicesByDate difference last month compared to this month = CALCULATE(COUNTROWS('Test');DISTINCT('Test'[Date]);DATEADD(DATESBETWEEN(Test[Date];STARTOFMONTH(Test[Date]);ENDOFMONTH(Test[Date]));-1;MONTH)) - CALCULATE(COUNTROWS('Test');DISTINCT('Test'[Date]);DATESINPERIOD(Test[Date];LASTDATE(Test[Date]);-1;MONTH)) 

     

    Best,

    Martin

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      trotsale Did it work out for you?

      • trotsale's avatar
        trotsale
        Regular Visitor
        9 years ago

        Thank you so much for help! 

        It works now, but it displays uptake only when graphic is shown by quarter or year. When I choose display by month, It shows me number of devices. Maybe I should change dates period?By year

         

         

        By month

         

         

  • trotsale's avatar
    trotsale
    Regular Visitor
    9 years ago

    I`ve tried this and here is what i got:

     

     Here is the way I used DAX: 

     

     My DateTable consists of 2 dates (07/21/2017 and 08/14/2017). Am I doing something wrong?