Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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?

 

 

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-yingjl
        Community Support
        5 years ago

        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.