Return a column value if inside a date range set by a filter

[KLGRIZZARD]

Below laid out is the best way that I can describe what I am trying to acomplish.

Table Name: Projects

Project Name	User	Start	End	Hours Per Week
Test Project #1	Kiel	01/31/2022	02/18/2022	10
Test Project #2	Kiel	02/07/2022	02/18/2022	8

Table Name: Fiscal Calander (this table is every calander date with associated fiscal info)

Fiscal Year	Fiscal Month	Fiscal Week	Date
2022	1	1	01/30/2022
2022	1	1	02/01/2022
2022	ect....	etc...	etc...
2022	1	3	02/18/2022

Would like a matrix report that somthing like this: (Using fiscal weeks as columns

(Fiscal Week ->)	1	2	3	4	5	6	7
User
Kiel	10	18	18	0	0	0	0

Not sure how to go about this, any help would be greatly approciated. Thanks. 

[tackytechtom]

Yes, it would, only to remove nearly all of the rows in the next step.... 

I think there is a way to get all weeks between two dates straight away as well.

Maybe you'll find something here:

Fill dates between dates with Power BI / Power Query — The Power User

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[tackytechtom]

Hi @KLGRIZZARD ,

My suggestion is to "unfold" the project table so the granularity would be one row per week and project name.

A potential way to do this in Power Query:

1. Create dummy rows for all days between Start and End Date, i.e. by creating a Custom Column and using this code snippet:

{ Number.From([Start])..Number.From([End]) } 

2. Expand the list

3. Inner Join with your Calendar Week table to get rid of the unnecessary days.

Hope this helps! 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[KLGRIZZARD]

Would creating the additional "dummy rows" essentially duplicate the other info in that table?