Matrix/Pivot with a Date Time Column

[MadhavDholakia]

Hello,

I have a data set having columns as Customer (Text), Resolved Date (Date Time) and a measure FCR %.

I would like to create a matrix/pivot with Customers as Row, Month-Year as columns (for the last 13 months - if I see this today, it will be Aug-2023 to Aug-2024) and FCR% in the value.

Can you please suggest if/how this can be achieved? - if I try with the current setup, columns are giving complete date time (and not group by Month-Year).

Thank you.

[rajendraongole1 · Accepted solution]

Hi @MadhavDholakia -In the table containing your Resolved Date column, add this DAX calculated column

MonthYear = FORMAT([Resolved Date], "MMM-YYYY")

To show only the last 13 months (from the current date), you can create a Date Table with a calculated column that filters the dates dynamically.

DateTable = CALENDAR(DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), TODAY())

you can add a calculated column to this Date Table to keep only the last 13 months:

IsLast13Months =
IF(
[Date] >= EDATE(TODAY(), -12) && [Date] <= TODAY(),
1,
0
)

This will return 1 for dates within the last 13 months and 0 otherwise.

Hope this helps.

[rajendraongole1 · Accepted solution]

Hi @MadhavDholakia -In the table containing your Resolved Date column, add this DAX calculated column

MonthYear = FORMAT([Resolved Date], "MMM-YYYY")

To show only the last 13 months (from the current date), you can create a Date Table with a calculated column that filters the dates dynamically.

DateTable = CALENDAR(DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1), TODAY())

you can add a calculated column to this Date Table to keep only the last 13 months:

IsLast13Months =
IF(
[Date] >= EDATE(TODAY(), -12) && [Date] <= TODAY(),
1,
0
)

This will return 1 for dates within the last 13 months and 0 otherwise.

Hope this helps.

[MadhavDholakia]

thanks @rajendraongole1 - almost there. can you please suggest how I can sort this incorrect order?

[Anonymous · Accepted solution]

Thanks for @rajendraongole1 's reply.
Hi @MadhavDholakia ,

You can try the following steps.

Create another calculated column in the same table that is used to sort the MonthYear calculated column.

Sort-MonthYear = FORMAT([Resolved Date], "MMM-YYYY")

Then select the /Month-Year/ calculated column and click the /SortByColumn/ button to select the sorted column just created.

Then return to the matrix visual and the MonthYear column should be sorted in the correct order.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MadhavDholakia]

thanks @Anonymous