Display a daily distinct count, using data from a MsSQL Temporal table with SCD

[Harald]

Hi, first post here.

My question is as follows, I have a temporal table which contains info about computers and their OperatingSystemBuild:

ObjectGUID	OperatingSystem	OperatingSystemBuild	SysStartTime	SysEndTime
1	Windows 10 Enterprise	17134	2020-11-06 15:11:18.1163711	9999-12-31 23:59:59.9999999
2	Windows 10 Enterprise	17134	2020-11-06 15:11:27.1594675	2020-11-10 03:05:25.3167793
2	Windows 10 Enterprise	19041	2020-11-10 03:05:25.3167793	9999-12-31 23:59:59.9999999
3	Windows 10 Enterprise	17134	2020-11-06 15:11:22.7371760	9999-12-31 23:59:59.9999999
4	Windows 10 Enterprise	17134	2020-11-06 15:11:19.5519881	9999-12-31 23:59:59.9999999
5	Windows 10 Enterprise 2016 LTSB	14393	2020-11-06 15:11:21.7557339	2020-11-12 03:01:18.5526743
5	Windows 10 Enterprise 2016 LTSB	17134	2020-11-12 03:01:18.5526743	9999-12-31 23:59:59.9999999
6	Windows 10 Enterprise	19041	2020-11-06 15:11:16.9236479	2020-11-13 03:00:37.6708962
6	Windows 10 Enterprise	19042	2020-11-13 03:00:37.6708962	9999-12-31 23:59:59.9999999

My goal is to create a visual which displays the count of builds per day like this:

The problem is that the Temporal table uses DateTime ranges to store the dimensions. So when I want to count the builds for a specific day, I need to check if for that day the build for a computer falls in the SysStartTime/SysEndTime range.

Can you help me with a Measure (or other solution) so I can display the count of each build for each day, using the data from the temporal table?

Thank you in advance.

[v-janeyg-msft · Accepted solution]

Hi, @Harald 

It’s my pleasure to answer for you.

According to your description,I think you need to crate a date table ,then create a measure used in 'values'.

Like this:

measure =
COUNTROWS (
    FILTER (
        Computers,
        [SysStartTime] <= MAX ( 'Table'[Date] )
            && [SysEndTime] >= MAX ( 'Table'[Date] )
    )
)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Harald]

Hi Janey,

Thank you for your answer, this solved my problem.

In my original solution I already had a date table, so your solution worked inmediately.

Greetings Harald