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.
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-2020
What 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 2020
This of course, is assuming that it CAN be done, and that this is the best way to give me sliceable data?
Solved! Go to Solution.
@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
@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
There was a date table flying around, I just couldn't figure out how to manage the relationships - but your solution did the trick - thanks @tex628 ! 🙂
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
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 |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |