Forum Discussion
Convert List Table into usable data
Yes, exactly what you've said.
To confirm it is going to work before you go to too much effort, you could just duplicate the main fact table and call one 'open' and one 'closed', join on the date table and build the visuals making sure you use the date from the date table. You may need to add a date only column for the resolve date so you have an appropriate type to join on (date to date).
One thing I noticed from the screenshots, you may want to add a YearWeek column as an integer, e.g. 202119 this will give you something to use as a 'Sort by' column for your 'Year Week' text column (although may not be necessary given the way you've formatted it).
Making this change to have two fact tables joined by the date table is what I'm referring to as "appropriate".
Let me know if you want the code for a PQ version of a date table.
Hi,
I'd like to help. Share a smaller dataset and show the expected result in a Table format. Once my answers tally with what you show in the expcted result, we can always switch to our desired visual. Also, since you want to show your visual by weeks, please also share a 2 column Caledar table with Date in the first column and week numbers in the second column. The Dates in the Calendar column should cover the span of dates in the 2 date columns appearing in your dataset.