Forum Discussion
Post PatronAdding a common year column to a table with two date columns... need to slice
This is only a small part of a very big picture, but I hope I can explain the problem correctly. Using a seperate date table won't work for this since a) my date table is using it's relations on other tables, and b) I need to slice both date columns and they're in the same table.
The table looks something like this:
ServiceID StartDate StopDate
11111 01-01-2019 01-05-2019
11112 02-06-2019
11113 05-12-2018 04-01-2020What I need to do is show the number of StartDate and StopDate, by Year
2018 - 1 StartDate, 0 StopDate
2019 - 2 StartDate, 1 StopDate
2020 - 0 StartDate, 1 StopDate
This needs to be done in the same visualisation (basic bar graph), meaning that I need a common axis for the years. The user should also be able to use a slicer to choose the year.
What I'm considering, is adding a new column with common year, so that it ends up like this:
ServiceID StartDate StopDate Year
11111 01-01-2019 01-05-2019 2019
11112 02-06-2019 2019
11113 05-12-2018 2018
11113 04-01-2020 2020This of course, is assuming that it CAN be done, and that this is the best way to give me sliceable data?
grggmrtn,
My advice would still be to create a seperate calendar if you don't already have one. 🙂
Create a calendar and then make 2 inactive relationships from Calendar[Date] to startdate / enddate.
Finally create 2 measures. One counting rows using the startdate as the active relationship and one using enddate.
Should look something like this:Count_Startdate = CALCULATE( COUNTROWS('Table') ; USERELATIONSHIP( 'Table'[StartDate] ; 'Calendar'[Date] )
Br,
J
3 Replies
- tex628
Community Champion
grggmrtn,
My advice would still be to create a seperate calendar if you don't already have one. 🙂
Create a calendar and then make 2 inactive relationships from Calendar[Date] to startdate / enddate.
Finally create 2 measures. One counting rows using the startdate as the active relationship and one using enddate.
Should look something like this:Count_Startdate = CALCULATE( COUNTROWS('Table') ; USERELATIONSHIP( 'Table'[StartDate] ; 'Calendar'[Date] )
Br,
J - amitchandak
Super User
Refer to my blog, how two dates can be mapped with a same time dimension and you do various month and year calc.
HR-Analytics-Active-Employee-Hire-and-Termination-trend
Your date seems to be in dd-mm-yyyy format make sure they have been read as a date, not text
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
