Matrix or Table format

[Anonymous]

Hello,

I've learn alot from this forum, but I think I got stuck on this scenario.

So I have two tables

Budget

Week	Department	$
1	A	50
1	B	50
1	C	70
2	A	60
2	B	60
2	C	70
3	A	80
3	B	80
3	C	90

Labor

Date	Department	Amount
3-Jan	A	10
4-Jan	A	10
5-Jan	B	20
6-Jan	B	10
7-Jan	C	10
8-Jan	C	20
9-Jan	A	10
10-Jan	A	20
11-Jan	A	20
12-Jan	B	10
13-Jan	B	10
14-Jan	C	10
15-Jan	C	10
16-Jan	A	10

But the one that hold me on this project is the format required.
How can I make it look like this?

Deparment = the unique departments

Budget = this is from Budget Table

Week Column = this is the total of the Dynamic Dates
Dynamic Dates (highligted Yellow) = this will change depending on week on the top. 

PBIX File : Link 

Please help. 
Thanks

[amitchandak]

@Anonymous , From week in the first table you have create a week start or end date.

Then you have create a date table with week details and join both tables on that

And use date and week number from there. Also you need to create common Dimension Table.

new column in budget

Week to Date = var _st = date(2021,1,1) //or user year column date([year],1,1)
var _week = [week]
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

For department - https://youtu.be/Bkf35Roman8

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482

[Anonymous]

My main issue here is how can i create a measure that will calculate all of this with this Column Header?

So all of those will be in one matrix.

[v-yingjl]

Hi @Anonymous ,

Based on your description, I'm afraid you could not achieve with creating one measure currently in power bi desktop becasuse [Hierarchy] and [Date] field are not in the same hierarchy definition.

In addition, in martrix visual, only fact columns and calculated columns can be put in the column fields, measures could not be put in it.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.