DAX unique count 5 day rolling total

[domleps]

Hi there,

I need some help with this dax measure formula please

I have the data set table on the left hand side - showing continuous periods of time flagged by an instance_id

I need the DAX to calculate the field i've highlighed in yellow - which is basically a unique count of the instance ID on a rolling 5 DAY basis.

e.g. - the value of 3 found on date 16/01/2020 is found by looking up the unique ids in the data set between 12/01/2020 and 16/01/2020 (5 day period) - and finding that there are three unique ids (3, 4 and 5) - there are two 4s, but it ignores one due to unique count

Is someone able to help with this?

Many thanks

[domleps]

thanks @Jihwan_Kim  - i've accepted this as a solution for this question.

However, I am still having issues as I am not able to use a measure to complete this calculation - i need to use a calculated column and I also am not able to use a separate date column as you have outlined in your solution.

I've created another post with a different version of the question - if you are able to take a look:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/7-day-count-and-distinct-count-by-worker-in-a...

[Jihwan_Kim · Accepted solution]

unique instance count 5 days rolling total : =
CALCULATE (
DISTINCTCOUNTNOBLANK ( Data[instance_id] ),
DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -5, DAY )
)

Link to the sample PBIX file