cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Earliest Date of a Case and Change in count between dates

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!
1 ACCEPTED SOLUTION
Community Support

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.

6 REPLIES 6
Community Support

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.

Community Champion

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

Regards,

Harsh Nathani

Helper I

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

Community Champion

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!!
Helper I

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.

Community Champion

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!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors