Displaying the data Year over Year in Matrix

[Anonymous]

I  wanted to display the data in Matrix table Year over Year:

sample out put of the report should look like as below.

Previous Year Week vs Current year week	4/5/2019	4/6/2020	4/12/2019	4/13/2020	4/19/2019	4/20/2020	4/26/2019	4/27/2020	April 2019	April 2020	Trend
Units	12	19	24	22	22	12	13	14

[Anonymous]

Hi @Anonymous,

You can extract your data values and add a custom index column to setting custom sort order or date fields. Then you can link to the original table and use new table fields on matrix to enable custom sort order:

Custom Sorting in Power BI  

If you confuse about these steps, please share some dummy data to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

I tried this option,but didnt work. Can you please create a sample PBI file based on some sample data. Like

Units : Date: 

12       01/01/2019

13       01/02/2019

15       01/03/219

so on    until 12/31/2020

[Anonymous]

HI @Anonymous,

The sorting table should be ranked based on month and year part, you can consider using isonorafter function to achieve your requirement:

Sort = 
SUMMARIZE (
    ALL ( 'Table' ),
    [Date],
    "Index", COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            ISONORAFTER (
                    MONTH ( [Date] ), MONTH ( EARLIER ( 'Table'[Date] ) ), DESC,
                    YEAR ( [Date] ), YEAR ( EARLIER ( 'Table'[Date] ) ), DESC
            )
        )
    )
)

I also attached the sample file below, you can check it if it helps.

Regards,

Xiaoxin Sheng

[Anonymous]

Perfect ,this logic is working fine .thanks a lot.

Trying to display sum value as well at the of the matrix report like 2019 vs 2020 total sales at the end of the row.How to embed this logic into current one?

[Anonymous]

Hi @Anonymous,

You can try to use below formula to append year to sort table.

Sort = 
VAR summary =
    SUMMARIZE (
        ALL ( 'Table' ),
        [Date],
        "Index", COUNTROWS (
            FILTER (
                ALL ( 'Table' ),
                ISONORAFTER (
                        MONTH ( [Date] ), MONTH ( EARLIER ( 'Table'[Date] ) ), DESC,
                        YEAR ( [Date] ), YEAR ( EARLIER ( 'Table'[Date] ) ), DESC
                )
            )
        )
    )
VAR yearList =
    ADDCOLUMNS (
        DISTINCT ( SELECTCOLUMNS ( summary, "Date", YEAR ( [Date] ) ) ),
        "Index",
        VAR offset =
            MINX ( summary, YEAR ( [Date] ) )
        VAR maxindex =
            MAXX ( summary, [Index] )
        RETURN
            [Date] - offset + maxindex + 1
    )
RETURN
    UNION (
        SELECTCOLUMNS ( summary, "Date", FORMAT(  [Date],"mm/dd/yyyy" ), "Index", [Index] ),
        yearList
    )

Notice: sort table existed both date type and number type values, so its date field can only be configured as text format.
For this scenario, you need to write a measure formula to use current category label to find out the corresponding value and use to replace the original value field and use on matrix visual.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks a lot. I was trying to ammendent the sort formula ,but didn't work out for me.

Trying to understand below formula how does it help. What you mean by creating by another measure? Can you please help here.