Map the date to a period

[Silvia_Wu]

Hi, lovely people!

I'm looking for a solution to map a date to a period:

Input:

From	To	Sprint #
01/01/2022	15/01/2022	Sprint 1
16/01/2022	30/01/2022	Sprint 2
31/01/2022	14/02/2022	Sprint 3
15/02/2022	01/03/2022	Sprint 4
02/03/2022	16/03/2022	Sprint 5

I want to map a date to get its "Sprint #", an example of the output is, for example, for the date "03/01/2022", I want to get "Sprint 1" as the output:

Date	Sprint
03/01/2022	Sprint 1
18/01/2022	Sprint 2
02/02/2022	Sprint 3
17/02/2022	Sprint 4
04/03/2022	Sprint 5

I know it is possible to write "if" to get that, but because my list is very long (more than 10 "sprints" to map), I'm not sure if "if" function is a good solution.

Any other ways?

Thank you in advance!

[RKM]

Hi @Silvia_Wu 

There are several ways. If possible I'd recommend that you do the calculation in your source view (SQL View) etc. and then pull in PBI or if that's at all not possible then you may calculate in Power BI too. Not really very expensive but depends how big you data volume is.

I can't upload files from my machine but can share a screen for you with your required look up column query. See if it helps...

Please mark as "Answered" if it solves your problem.

[ValtteriN · Accepted solution]

Hi,

With input data in the format you descibed you can create a mapping table e.g. by creating a list where you have dates associated with a spint. Then you can create a realtionship between this tables date and your fact tables date. Here is a good video by curbal which should help you to modify the data to a good format: https://www.youtube.com/watch?v=u3HLseZxSj4

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/