Forum Discussion
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 looks like this:
I already have a measure which counts number of devices by date, it seems like
DevicesByDate = CALCULATE(COUNTROWS('Table');DISTINCT('Table'[Date]))But I can`t get a measure which will substract number of DevicesByDate from previous date (Example: 4 devices by 08/15/2017-4 devices by 07/21/2017 = 0).
Can you please help me guys?
Thanks!
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
5 Replies
- 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.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
trotsale Did it work out for you?
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?
