Calculate row differences under different category

[ronlolololo]

Hi Team, I am looking for solution for this issue. 

I would want to calculate the difference between each day under each county. Wondering how to achieve this. Please advised. Thanks. 

[ronlolololo · Accepted solution]

Hi guys, 

I solved the issue via this method. https://community.powerbi.com/t5/Desktop/Dax-to-Sql-Windowing-Lag-function-Over-partition-by-equival...

[v-rzhou-msft]

Hi @ronlolololo 

I'm so glad you have solved your porblem. And thanks for sharing this method for us to solve this issue. 

Please mark your reply as a solution.

Best Regards,

Rico Zhou

[Ashish_Mathur]

Hi,

Try this calculated column formula

=Table[DateKey]-calculate(max([datekey]),filter(table,table[County Name]=earlier(table[County Name])&&table[DateKey]<earlier(table[DateKey])))

Ensure that your Datekey column is formatted as a Date field.

Hope this helps.

[ronlolololo]

Hi @Ashish_Mathur 

Thanks for the response!

I typed in the code and it tured out this way. Let me know if I did anything wrong. Thanks.

[Ashish_Mathur]

It looks fine to me.  Share the link from where i can download your PBI file.

[lbendlin]

I see you have an index column - that's good.

1. for each line in your visual (the filter context) get the county name

var cn = SELECTEDVALUE(table[County Name])

2. get the current index

var id = SELECTEDVALUE(table[Index])

3. get the maximum index for the same county that is smaller than your current index

var prev_id=CALCULATE(MAX(table[Index]),ALLSELECTED(table),table[County Name]=cn,table[Index]<id)

4. lastly, get the value for that index.

var val = CALCULATE((whatever),ALLSELECTED(table),table[Index]=prev_id)