Conditional Formatting Latest Windows OS Build

[pvravestein]

Hi,

I'm trying to do the following.

I have the Intune Datawarehouse which contains a lot of data including the OS Version.

In my PBI I split that OS Version to get the right Build versions.

For the report I want to compare the Build Version (OsVersion - Copy.4) with the numbers from Microsoft.

For this purpose I copy the figures from Microsoft (Windows 10 - release information | Microsoft Docs) to a table.

Now I want to update the report based on the latest Build update, so when I change the Microsoft table all the numbers change with it in my report. (Like this, manually set the colors)

So I think I need a measure and use this measure in the conditional formatting setting in the report? 

Can someone help? Or even make it easier and use the numbers from Microsoft directly?

Thanks

[pvravestein · Accepted solution]

Got some sort of solution. Now I'm using a Web connection to get the right information for the Windows updates.

Now I always see the current CU in my visuals.

[V-lianl-msft]

Hi @pvravestein ,

Please create a measure like below and set the data colour.

Measure = IF(DATEDIFF(MAX('OS build'[Availability date]),TODAY(),WEEK)<4,"GREEN","BLUE")

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

[pvravestein]

Hi @V-lianl-msft ,

This is a nice solution and excellent explained but I cant get it to work. I need the Latest Revision Date as reference for the latest build version and compare that to the OS version. Offcourse I played around with the measure and the latest revision date but unfortunately it does not work.

Thanks again

[V-lianl-msft]

Hi @pvravestein ,

Do you create a one to many relationship based on the OS versions in both tables?

Any chance you can share a PBIX file with dummy data?

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... 

Please remove any sensitive data before uploading.

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

[pvravestein]

I'm sorry for my late reaction, I had some holiday leave.

I made a sample Pbix for you to work with. Thanks again

Sample pbix