Daily Values from Running Total

[blackgold]

Hello,

I am attempting to get daily values from the running total.
I have three columns Key, Date, Cumulative Cases. I am trying to get the daily values per key.

I have attached a picture from excel of what i am trying to do in PowerBI. However, I can not do it on excel because the data has over 3 million rows. 

Keeping the performance in mind, which will better DAX or power query?

Update

I have come up with a solution which works with a sample dataset but when I try it with the actual dataset the DAX formula stays on "working on it".

I appreciate the help.

Thank you!

[aj1973]

@blackgold 

Definetly DAX is your best option 

[VahidDM]

Hi @blackgold 

Can you post sample data as text not image to be able to copy/paste that?

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[blackgold]

Key	Date	Cumulative Cases
Baldwin, Alabama	4/10/2022	64
Baldwin, Alabama	4/11/2022	70
Baldwin, Alabama	4/12/2022	75
Baldwin, Alabama	4/13/2022	82
Autaga, Alabama	4/10/2022	18
Autaga, Alabama	4/11/2022	19
Autaga, Alabama	4/12/2022	19
Autaga, Alabama	4/13/2022	20
Tyler, Texas	4/10/2022	87
Tyler, Texas	4/11/2022	89
Tyler, Texas	4/12/2022	95
Tyler, Texas	4/13/2022	102

[blackgold]

Hello @VahidDM,

I have updated the question. 
I come up with solution but it is only working on the sample dataset. When I use the same DAX formula on the actual dataset, PowerBI is stuck on working on it. 
What will suggest on optimizing the DAX formula?
Thank you!

[aj1973]

@blackgold 

Why not using it as a Measure instead of a calculated column. The DAX will work just fine into a measure.

[blackgold]

I tired that and I get an error saying:

A single value for column 'Key' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[aj1973]

Did you modify the measure in the picture above to get it work as a Measure? Like in the Return, did you add Sum(Table...)

Try to evaluate your VAR one by one until you get to know which one needs a little modification..... remember to keep the logic of your calculated column.

[blackgold]

What function should I use 'Table'[key] since it is a string?