Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
webportal
Impactful Individual
Impactful Individual

Calculate the number of Covid.19 cases on the date the disease was found by country

How to get the number of a given column for a specific date?

I'm trying to calculate the number of Covid-19 confirmed cases per country on the first day the disease has been detected.

This first day varies by country and it is correctly calculated as:

Data do 1º caso = CALCULATE(MIN('Covid19'[Data]);FILTER('Covid19';[Total Casos Confirmados]>0))

The expression Total Casos Confirmados above is:

Total Casos Confirmados = CALCULATE(SUM('Covid19'[CasosConfirmados]);LASTDATE('Covid19'[Data]))

And the measure I'm trying to calculate is:

Número de casos no primeiro dia = CALCULATE(SUM('Covid19'[CasosConfirmados]);FILTER('Covid19';'Covid19'[Data]= [Data do 1º caso]))

And this is how Covid19 table looks like:

UiqB7.png

 

 

And the Wrong result:

 

24MOg.png

 

Since the table is a time series with cumulated values, the measure is returning the acumulated value for all the dates..

 

1 ACCEPTED SOLUTION

@webportal 

Try like

Measure = 
VAR __id = MIN ( 'Covid19'[Country/Region] )
VAR __Data = CALCULATE ( MIN( 'Covid19'[Data] ), ALLSELECTED ( 'Covid19' ),  'Covid19'[Country/Region] = __id ,'Covid19'[CasosConfirmados]>0) 
RETURN CALCULATE ( Sum ( 'Covid19'[CasosConfirmados] ), VALUES ( 'Covid19'[Country/Region] ), 'Covid19'[Country/Region] = __id, 'Covid19'[Data] = __Data )

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@webportal , for case on first date country wise

Measure = 
VAR __id = MIN ( 'Covid19'[Country/Region] )
VAR __Data = CALCULATE ( MIN( 'Covid19'[Data] ), ALLSELECTED ( 'Covid19' ),  'Covid19'[Country/Region] = __id ) 
RETURN CALCULATE ( Sum ( 'Covid19'[CasosConfirmados] ), VALUES ( 'Covid19'[Country/Region] ), 'Covid19'[Country/Region] = __id, 'Covid19'[Data] = __Data )

 

Thanks a lot, but not really there because I get zeros as results for certain countries, so I guess the calculation is being done for the 1st absolute date and not the 1st date of each country.

@webportal 

Try like

Measure = 
VAR __id = MIN ( 'Covid19'[Country/Region] )
VAR __Data = CALCULATE ( MIN( 'Covid19'[Data] ), ALLSELECTED ( 'Covid19' ),  'Covid19'[Country/Region] = __id ,'Covid19'[CasosConfirmados]>0) 
RETURN CALCULATE ( Sum ( 'Covid19'[CasosConfirmados] ), VALUES ( 'Covid19'[Country/Region] ), 'Covid19'[Country/Region] = __id, 'Covid19'[Data] = __Data )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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