Power BI - setup day 1 based on value in another column

Question

I have table in Power BI with following columns: Country, Date and event. I would like to add measure or new column if needed with 'Start Day'. 'Start Day' assignes value '1' at the first day that value greater than 0 apears in column 'event' and starts counting days from this date. In example below for country UK value 1 in column 'event' apears on 19/03/2020 and in column 'Start Day' value 1 is assigned. Next date 20/03/2020 has value 2 assigned and so on. What is the best way to do this in Power BI?

Robert

Answer 1

You need to create an extra column as this is static data.

You can do this by first getting the start date of that country and then take the datediff between the dates.

In the CALCULATE function, I get all rows where the country is equal and where the event = 0. From those rows, I get the max date (firstD). This was the most important step as we can now use the DATEDIFF to calculate the days.

I used the max function once more because you want to have 0 in the rows before the startdate.

Start Day = 
var country = events[Country]
var firstD = CALCULATE(MAX(events[Date]);FILTER(events;events[Country] = country && events[event]=0))
return max(DATEDIFF(firstD;events[Date];DAY);0)