Forum Discussion

MadhavDholakia's avatar
MadhavDholakia
Helper I
1 year ago
Solved

Matrix/Pivot with a Date Time Column

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.

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

4 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.