Forum Discussion
Sort Data using Monthly days with timestamp filter
I have a table with Daily data collection.
| Month | January | February | ||||
| Day | Car | Bus | Trucks | Car | Bus | Trucks |
| 1 | 1507 | 69 | 0 | 730 | 43 | 100 |
| 2 | 2761 | 29 | 618 | 1024 | 21 | 79 |
| 3 | 2245 | 103 | 418 | 1609 | 3 | 37 |
| 4 | 2237 | 77 | 463 | 943 | 35 | 105 |
| 5 | 1863 | 92 | 765 | 901 | 26 | 21 |
| 6 | 2453 | 93 | 835 | |||
| 7 | 2334 | 88 | 0 | |||
| 8 | 2731 | 58 | 718 | |||
| 9 | 1255 | 112 | 1017 |
After loading it to PowerBI desktop, I have transformed it into :
| Month | Attribute | Day | MonthNum | Value |
| January | Bus | 1 | 1 | 69 |
| January | Bus | 2 | 1 | 29 |
| January | Bus | 3 | 1 | 103 |
| January | Bus | 4 | 1 | 77 |
| January | Bus | 5 | 1 | 92 |
| January | Bus | 6 | 1 | 93 |
| January | Bus | 7 | 1 | 88 |
| January | Bus | 8 | 1 | 58 |
| January | Bus | 9 | 1 | 112 |
| January | Car | 1 | 1 | 1507 |
| January | Car | 2 | 1 | 2761 |
| January | Car | 3 | 1 | 2245 |
| January | Car | 4 | 1 | 2237 |
| January | Car | 5 | 1 | 1863 |
| January | Car | 6 | 1 | 2453 |
| January | Car | 7 | 1 | 2334 |
| January | Car | 8 | 1 | 2731 |
| January | Car | 9 | 1 | 1255 |
| January | Trucks | 1 | 1 | 0 |
| January | Trucks | 2 | 1 | 618 |
| January | Trucks | 3 | 1 | 418 |
| January | Trucks | 4 | 1 | 463 |
| January | Trucks | 5 | 1 | 765 |
| January | Trucks | 6 | 1 | 835 |
| January | Trucks | 7 | 1 | 0 |
| January | Trucks | 8 | 1 | 718 |
| January | Trucks | 9 | 1 | 1017 |
| February | Bus | 1 | 2 | 43 |
| February | Bus | 2 | 2 | 21 |
| February | Bus | 3 | 2 | 3 |
| February | Bus | 4 | 2 | 35 |
| February | Bus | 5 | 2 | 26 |
| February | Car | 1 | 2 | 730 |
| February | Car | 2 | 2 | 1024 |
| February | Car | 3 | 2 | 1609 |
| February | Car | 4 | 2 | 943 |
| February | Car | 5 | 2 | 901 |
| February | Trucks | 1 | 2 | 100 |
| February | Trucks | 2 | 2 | 79 |
| February | Trucks | 3 | 2 | 37 |
| February | Trucks | 4 | 2 | 105 |
| February | Trucks | 5 | 2 | 21 |
Now I have this graph showcasing attribute (bus in the below case) wise trends, monthly on daily basis:
Ask 1 : For the same graph, I need it to be January 1 to 31st and then February daily data to get plotted.
I have a filter for the report which is Timesptamp based, so I have created a relationship This table(Day) with Timestamp based table(Day). So, when I filter from 1st Feb to 5th Feb, I get data of Day 1 to 5 for both January and February
Ask 2 : How could I sort my data, so that my timesptamp filter could give me only selected dates data.
Hi TejasShah ,
You need to go ahead and create a date column, and use it instead of Table [Day] to create a relationship with Timestamp [Date].Be sure to place [Month] above the [Day] hierarchy here.
Best Regards,
Gao
Community Support TeamIf 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
1 Reply
Hi TejasShah ,
You need to go ahead and create a date column, and use it instead of Table [Day] to create a relationship with Timestamp [Date].Be sure to place [Month] above the [Day] hierarchy here.
Best Regards,
Gao
Community Support TeamIf 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