Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    I found the solution on another post.

     

    Source

     

    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.

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
8 years ago

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,
Cherry

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi 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.