Skip to main content
cancel
Showing results for 
Search instead 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

Reply

Earliest Date of a Case and Change in count between dates

So I have the following type of data:

 

LocationDateCase CountEarliest Date
A1/1/20200 
A1/2/202031/2/2020
A1/3/2020101/3/2020
B1/1/20200 
B1/2/20200 
B1/3/202011/3/2020
B1/4/202081/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)
LocationDateCase CountEarliest DateChange in Count
A1/1/20200 --
A1/2/202031/2/20203
A1/3/2020101/2/20207
B1/1/20200 --
B1/2/20200 --
B1/3/202011/3/20201
B1/4/202081/3/20207
Thanks!
1 ACCEPTED SOLUTION
v-xicai
Community Support
Community Support

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:

106.png

 

 

 

 

 

 

 

 

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.

View solution in original post

6 REPLIES 6
v-xicai
Community Support
Community Support

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:

106.png

 

 

 

 

 

 

 

 

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
Community Champion
Community Champion

Hi @william_johnson ,

 

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

 

Regards,

Harsh Nathani

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

 

 

Hi @william_johnson ,

 

Thanks a lot. Here you go.

 

1.jpg

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

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. 

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

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.