I am currently working a dashboard for an institution that has three different terms per year. They’d like to compare their actual progress to the past term (-1) and to the correspondent to the past year (-3), however when comparing to the past term, week numbers don’t match. For example:
And I’d like to achieve this (This I’ve achieved it by using a lookup table so that I can match the weeks from the past term to this one):
However, my question is if this is achievable from the original date table without the need to use an additional look up table, if this would be the best practice, and how could it be done?
I’d appreciate any help.
PosibleReporteDeBajas23-2.pbix (2.1 MB)
Bumping this post for more visibility from our experts and users.
It looks like you are mapping any week 3 and 19 to period 19 so week 3 and 19 in 2022 will be counted in period 19, if thats the logic, then what you have done would work fine, no need to change anything, it would allow granular control of your periods, if you want to add a calculated column in your date table then give it a go, you could use a SWITCH(TRUE(), … ) construct to do it.
Or is there a specific logic for assigning the periods.
@kylie.oconnell I do have say a start and finish date for each period; could go even deeper since we have different type of periods for a single term (retention period, admission period, lead acquisition period). I was thinking perhaps a calculated column establishing these specific weeks for said date, just not sure how I would make a relationship between these weeks so that I could match them accordingly so that when I filter a date range the visuals would show their according matching weeks for each of the periods (2022-2, 2023-1 and 2023-2).
I feel like I can get away for now with the way I did it (with a look up table) for now, however I would like to start looking into the right way to get it done.
Thanks for the help!
1 Like
if dates can be in multiple periods you could have a column in the date table for each type and the period of that category the date is in, it would work like normal periods such as months etc.
Another approach is where you have categories like last 6 months, last 3 months etc in a column of labels and the dates in another column, so the same date can be in multiple times, the relationship is a many to one bi directional relationship, but the label column can be used in visuals as a filter for all dates in the last 6 moths etc.
if you have a table for each type of period than contain the dates, and the date is only in one period, you could load them into power query and merge against the date expand the column and name it retention period, rinse and repeat for each period.
1 Like
Hello @juanpcl1996
Did the responses above help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
