Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grggmrtn
Post Patron
Post Patron

Adding 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-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?

 

 

1 ACCEPTED SOLUTION
tex628
Community Champion
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


Connect on LinkedIn

View solution in original post

3 REPLIES 3
tex628
Community Champion
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


Connect on LinkedIn

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 ! 🙂

amitchandak
Super User
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.