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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ironboy0418
Helper II
Helper II

What is the additional if statement that will return the first-non zero value from previous row?

I am currently doing a COVID-19 reports/dashboard through Power BI. I have this dax formula that will return the new cases for each day. However, the problem with this formula is that I am not able to return the value of the first case - it returns 0. May I ask for suggestion on how to make this formula return the value of first case as well?

 

Datasource: https://github.com/CSSEGISandData/COVID-19/blob/master/csse_covid_19_data/csse_covid_19_time_series/...

 

Source code:

New_Cases = 
VAR Countries = ConfirmedCases[Country/Region]
VAR State = ConfirmedCases[Province/State]
VAR dates = ConfirmedCases[Date]
VAR PrevDay =
CALCULATE(
    MAX(ConfirmedCases[Cases]),
    FILTER('ConfirmedCases',
    ConfirmedCases[Country/Region]=Countries && ConfirmedCases[Province/State]=State && ConfirmedCases[Date]<dates))
RETURN
IF(PrevDay=0,0,
IF(PrevDay>[Cases],0,
IF(PrevDay>0,[Cases]-PrevDay)))

Here is the current output: stackoverflow_dax01.PNG

 

1 ACCEPTED SOLUTION
ironboy0418
Helper II
Helper II

We got the answer! Sharing the appropriate solution:

 

New_Cases =
VAR Countries = Confirmed_Cases[Country/Region]
VAR State = Confirmed_Cases[Province/State]
VAR dates = Confirmed_Cases[Date]
VAR tbl =   FILTER('Confirmed_Cases',
    Confirmed_Cases[Country/Region]=Countries && Confirmed_Cases[Province/State]=State && Confirmed_Cases[Date]<dates)
VAR PrevDay =
CALCULATE(
    MAX(Confirmed_Cases[Cases]),
     tbl)
RETURN
[Cases] - PrevDay

View solution in original post

3 REPLIES 3
ironboy0418
Helper II
Helper II

We got the answer! Sharing the appropriate solution:

 

New_Cases =
VAR Countries = Confirmed_Cases[Country/Region]
VAR State = Confirmed_Cases[Province/State]
VAR dates = Confirmed_Cases[Date]
VAR tbl =   FILTER('Confirmed_Cases',
    Confirmed_Cases[Country/Region]=Countries && Confirmed_Cases[Province/State]=State && Confirmed_Cases[Date]<dates)
VAR PrevDay =
CALCULATE(
    MAX(Confirmed_Cases[Cases]),
     tbl)
RETURN
[Cases] - PrevDay
amitchandak
Super User
Super User

@ironboy0418 , Previous date when cases were 0 (New column)

 

New_Cases =
VAR Countries = ConfirmedCases[Country/Region]
VAR State = ConfirmedCases[Province/State]
VAR dates = ConfirmedCases[Date]
VAR PrevDay =
CALCULATE(
MAX(ConfirmedCases[dates]),
FILTER('ConfirmedCases',
ConfirmedCases[Country/Region]=Countries && ConfirmedCases[Province/State]=State && ConfirmedCases[Date]<dates && ConfirmedCases[New_Cases]=0))
RETURN
PrevDay

 

 

1st date when case were 0

 

New_Cases =
VAR Countries = ConfirmedCases[Country/Region]
VAR State = ConfirmedCases[Province/State]
VAR dates = ConfirmedCases[Date]
VAR PrevDay =
CALCULATE(
Min(ConfirmedCases[dates]),
FILTER('ConfirmedCases',
ConfirmedCases[Country/Region]=Countries && ConfirmedCases[Province/State]=State && ConfirmedCases[Date]<dates && ConfirmedCases[New_Cases]=0))
RETURN
PrevDay

Thanks amitchandak! Do I need to create two columns for these?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors