Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Matrix or Table format

Hello,

 

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

 

So I have two tables

 

Budget

WeekDepartment$
1A50
1B50
1C70
2A60
2B60
2C70
3A80
3B80
3C90

 

Labor

 

DateDepartmentAmount
3-JanA10
4-JanA10
5-JanB20
6-JanB10
7-JanC10
8-JanC20
9-JanA10
10-JanA20
11-JanA20
12-JanB10
13-JanB10
14-JanC10
15-JanC10
16-JanA10

 

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

 

Resultss..PNG

 

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

3 REPLIES 3
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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

pamboys09_0-1611246251561.png

 

So all of those will be in one matrix.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.