start a matrix table at 7am

[maurcoll]

Hi I have a matrix table that has hours in the column and store in the row with a count of employees as the value

Currently it defaults to start at midnight through to 11pm. I want it to start at 7am through to 6am. Is there a way to do this.

store	0	1	2	3	4	5	6	7
A	10	15	12	10	8	5	8	9

store	7	8	9	10	11	12	13	14
A	9	20	15	10	15	18	8	9

Thank you

[maurcoll]

Hi that works as it renames the 6 to 23 which is what i need but i still need to use the original column in my matrix and sort by that so in the matrix the 6 still needs to show as 6 not 23.
This is what i need it to look like, hours from midnight to 6am are from the following day. so i need the day to start at 7 am through to 6 am the next day on each row. Hope that makes sense. I have tried creating a conditional column and sort by but that doesnt seem to work either. The hour column how it is imported from teh DWH is stored as a number and i cant amend that.

	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	0	1	2	3	4	5	6
11/07/2024
12/07/2024
13/07/2024
14/07/2024
15/07/2024
16/07/2024

[v-cgao-msft]

Hi @maurcoll ,

If this error is encountered when sorting by another column, consider creating a custom column in PowerQuery.

if [Hour] >= 7 then [Hour] - 7 else [Hour] + 17

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[lbendlin]

I am sure that won't be confusing to your report users at all...

[maurcoll]

Thank you for such a helpful comment

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[AmiraBedh]

You can create a calculated column that adjusts the hour values to fit the desired range :

ShiftedHour = 
    IF(
        'Table'[Hour] >= 7, 
        'Table'[Hour] - 7, 
        'Table'[Hour] + 17
    )

Now you can use the ShiftedHour column instead of the original hour column.

[lbendlin]

yes, read about "Sort a column by another column".  This assumes you have a dedicated hour column in your table.