Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have a question about a date table where I'd like to format so I can get total count of people and total hours per date.
I got handled (picture, and filter the peoples names). So the week column continues until present week.
I wonder if anyone has a solution to fix dates for this. So example week 22 monday (May 31st) the total count of name is 8 and total hours 59. I've tried to invoke a date table, but it doesn't recognize that monday week 22 is that date and so on.
Thanks for any sort of help.
Solved! Go to Solution.
You need a date table, and your weeks need to have year indicators. Otherwise once you get 2 or more years, week 22 will be in different years.
When I get source data that only has info like this, adding the year is pretty easy. As for the date, you can use this method. It returns a table like this, that you can now create a relationship with a date table, and do your aggregations.
I did this in Excel, so Open up Power Query and look at the Table query.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou need a date table, and your weeks need to have year indicators. Otherwise once you get 2 or more years, week 22 will be in different years.
When I get source data that only has info like this, adding the year is pretty easy. As for the date, you can use this method. It returns a table like this, that you can now create a relationship with a date table, and do your aggregations.
I did this in Excel, so Open up Power Query and look at the Table query.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Thank you for your reply. It worked, I created an empty query and imported a date table there in the advanced query, made the connection to the data table. I thought date for date was necessary but just kept it simple and displayed everything by weeks 🙂
Great @Scaffnull - glad I was able to assist.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting