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 = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ) )
I then created 3 additional columns in the Date Table.
Start:
Start = COUNTAX(FILTER('Assets','Date'[Date]>='Assets'[Start Date]),'Assets'[ID])End:
End = COUNTAX(FILTER('Assets','Date'[Date]>='Assets'[End Date]),'Assets'[ID])Running Total:
Running Total = 'Date'[Start]-'Date'[End]
From this I was able to create a chart showing the number of Active Assets with a date slicer. The problem I'm running into now is that if I add slicers with columns from the Assets table, incorrect values show up. For example: I'm trying to filter the number of Assets active on Sept 1st, 2018 based in Ohio. If I select Ohio on my slicer, the total number of active assets appear, not just active Ohio assets.
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.
4 Replies
Hi Anonymous,
Have you create the relationship between the Assets table and the Date Table?
If it is conveient, could you share some data sample about your Assets table and the screenshots of the relationship?
Best Regards,
CherryHi v-piga-msft,
Here is a sample of my data. I'd like to be able to filter a specific date, and see the number of assets active on that day. Futhermore, I'd like to be able to further slice the data to see how many assets are from a particuar country or in the Technology category on that specific date.
I have a relationship set up from the Date table, column Date, to the Asset Table, column Start 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.
