Utilization using Calendar function

[Blizzardstyx]

Hi All,

I'm super close to solving my utilization problem per asset. I've come up with using a calendar function creating a seperate table to get the number of activities per day for the date range I'm interested in. I have figured out how to do it with one asset, now I'm trying to make it so I can have the sum of this solution for every asset. 

The core issue is that I'm trying to find out how many days of the month that a vehicle is utilized. I don't care about overlap, I don't care about number of activities, I just care if the asset was or was not used that day (of the month). In the file/picture below I'm halfway there, I just need the syntax to make my Tractors[Tractor ID] dynamic and to be placed on my Tractors table. 

I have the file I'm working with right here:

https://drive.google.com/file/d/1Y42_E-gwZ7syAJ2_IBE_-u3Y6XE77okI/view?usp=sharing

@v-alq-msft Hoping you can help

Thanks in advance

[V-lianl-msft]

Hi @Blizzardstyx ,

Regardless of the performance impact, you can create a calculated column with if or switch.

You can refer to the pbix.

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

[Blizzardstyx]

Hi @V-lianl-msft ,

The first tractor ID 1067 should show 9 days. (currently showing 😎

In the month of January it was utilized 1/1/2020-1/9/2020 over two activities. 

[V-lianl-msft · Accepted solution]

Hi @Blizzardstyx ,

Pbix has been updated, please refer to this pbix again.

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

[Greg_Deckler]

Could probably use GENERATE to generate a table that is the Cartesian product of your tractors and dates and then use ADDCOLUMNS to add your column on whether the tractor was ustilized on a particular day. Then it would be a matter of using SUMX or COUNTX and such to add those up versus all of the days in in the original calendar table.