Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |