Forum Discussion
Display Active Assets
I have a list of assets with start and end dates. I want to be able to view on any given date the number of active assets. I originally came up with a solution of adding a date table using: Date ...
I found the solution on another post.
First, I created another table
Detail = VAR temp = SELECTCOLUMNS('Assets',"ID",[ID],"Start Date", [Start Date], "End Date", [End Date]) VAR vCalendar = CALENDAR(MINX(temp,[Start Date]),MAXX(temp,[End Date])) RETURN SELECTCOLUMNS( FILTER( CROSSJOIN (temp,vCalendar), [Start Date] <= [Date] && [End Date]>=[Date]), "ID", [ID], "Start Date", [Start Date], "End Date", [End Date], "Detail",[Date])Then I made a relationship from the Assets table to the Detail table
From this I was able to make visuals using the Details table, while using slicers from the Assets Table.
I found the solution on another post.
First, I created another table
Detail =
VAR temp =
SELECTCOLUMNS('Assets',"ID",[ID],"Start Date", [Start Date], "End Date", [End Date])
VAR vCalendar =
CALENDAR(MINX(temp,[Start Date]),MAXX(temp,[End Date]))
RETURN
SELECTCOLUMNS(
FILTER(
CROSSJOIN (temp,vCalendar),
[Start Date] <= [Date]
&& [End Date]>=[Date]),
"ID", [ID],
"Start Date", [Start Date],
"End Date", [End Date],
"Detail",[Date])Then I made a relationship from the Assets table to the Detail table
From this I was able to make visuals using the Details table, while using slicers from the Assets Table.
How would you handle null date values? I have used this but my data has missing start and finish dates so I need to be able to adjust accordingly.