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
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
@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
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
103 | |
103 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |