Forum Discussion
Table with Column and Row identifiers
I have a table "Monthly Sales" that has the Sales Reps in rows and Months as Columns.
I need to calculate the current selected month sales amount per sales rep based on this data. The issue I am faced with is how do I tell Power Query that the columns are dates so I can write a measure to look for current selected month.
EX: If I selected a date of 10/20/2021 I would want to see the October amount for Nick and Hatcher.
Hi Anonymous ,
1.As AlexisOlson suggested, please click the Name column --> Select Unpivot other columns:
2. Create a date table, build relationships based on Month column. (I have renamed the Attribute column to Month)
ForSlicer = ADDCOLUMNS( CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Month", FORMAT([Date],"mmmm"))Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Unpivot the month columns to make the data easier to work with.
Hi Anonymous ,
1.As AlexisOlson suggested, please click the Name column --> Select Unpivot other columns:
2. Create a date table, build relationships based on Month column. (I have renamed the Attribute column to Month)
ForSlicer = ADDCOLUMNS( CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Month", FORMAT([Date],"mmmm"))Output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
