Earliest Date of a Case and Change in count between dates

[william_johnson]

So I have the following type of data:

Location	Date	Case Count	Earliest Date
A	1/1/2020	0
A	1/2/2020	3	1/2/2020
A	1/3/2020	10	1/3/2020
B	1/1/2020	0
B	1/2/2020	0
B	1/3/2020	1	1/3/2020
B	1/4/2020	8	1/4/2020

I want to show the earliest date and not have it change: This is the code I used to calculate the earliest date:

Earliest Date = Calculate(min([Date].[Date]), filter('table','table'[Case Count]>0)

I'd also like to have a running number of the change in count between dates by location. 

Expected Output (Edit)

Location	Date	Case Count	Earliest Date	Change in Count
A	1/1/2020	0		--
A	1/2/2020	3	1/2/2020	3
A	1/3/2020	10	1/2/2020	7
B	1/1/2020	0		--
B	1/2/2020	0		--
B	1/3/2020	1	1/3/2020	1
B	1/4/2020	8	1/3/2020	7

Thanks!

[v-xicai · Accepted solution]

Hi @william_johnson ,

You may create measures like DAX below.

Earliest Date = CALCULATE( MIN('Table'[Date]), FILTER(ALLEXCEPT('Table', 'Table'[Location]), 'Table'[Case Count] > 0 &&'Table'[Date] <= MAX('Table'[Date])))


Change in Count = Var d= CALCULATE(SUM('Table'[Case Count]),FILTER(ALLEXCEPT('Table','Table'[Location]),'Table'[Date]=[Earliest Date]))

return

IF(MAX('Table'[Case Count])<>0, IF(MAX('Table'[Date])=[Earliest Date],d , SUM('Table'[Case Count])-d))

Result:

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshnathani]

Hi @william_johnson ,

Please share the expected output. It will help provide a fastr resolution.

Regards,

Harsh Nathani

[william_johnson]

Thanks, changed! First time post, I'm a newbie!

[harshnathani]

Hi @william_johnson ,

Thanks a lot. Here you go.

Create Calculated Columns

Earliest Date = CALCULATE( MIN(Table7[Date]), FILTER(ALLEXCEPT(Table7,Table7[Location]), Table7[Case Count] > 0 && Table7[Date] <= EARLIER(Table7[Date])))

Demoted Case = CALCULATE( MIN(Table7[Case Count]), FILTER(ALLEXCEPT(Table7,Table7[Location]), Table7[Case Count] > 0 && Table7[Date] < EARLIER(Table7[Date])))

Demoted Case = CALCULATE( MIN(Table7[Case Count]), FILTER(ALLEXCEPT(Table7,Table7[Location]), Table7[Case Count] > 0 && Table7[Date] < EARLIER(Table7[Date])))

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

[william_johnson]

The demoted case only returns an answer of 1 rather than the actual number of cases on day 2 minus the number of cases on day 1 by location, and so-on. 

[harshnathani]

@william_johnson ,

Change in Count = Table7[Case Count] - Table7[Demoted Case]

Use this calculated column to find the change in Count by location each day.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!