Time Intellegence - Compare day of week in Matrix

[diddlmus]

Hi All

Im trying to figure out how the heck to get a Matrix to show this months sales pr. day comparing to last years sales on the same day of week.

I have all my sales figures in a table and a master calender table.

I would like to have a column showing this months date and then the sales for this year and previous year pr. day.

What measure to write to do that.

COLUMN: DATE (Days)

COLUMN this year sale

COLUMN last year sale

ROWS: This Months Full Date

Is it even possible to do?

[v-yuta-msft]

HI diddlmus,

Do you mean both this year sale and last year sale are measures? Where shall we put COLUMN: DATE (Days) and ROWS: This Months Full Date in matrix?

Assume in matrix this year sale and last year sale are measures which are placed in matrix visual Values bucket, and in Rows bucket put DATE column. You can use DAX formula below:

DATE =
DATESBETWEEN (
    EOMONTH ( EDATE ( TODAY (), -1 ), 0 )
        + 1,
    EOMONTH ( TODAY (), 0 ),
    DAY
)

this year sale =
VAR start_date =
    DATE ( YEAR ( TODAY () ), 1, 1 )
VAR end_date =
    DATE ( YEAR ( TODAY () ), 12, 31 )
RETURN
    CALCULATE ( MAX ( Sales[Sales] ), DATESBETWEEN ( start_date, end_date, day ) )

last year sale =
VAR start_date =
    DATE ( YEAR ( TODAY () ) - 1, 1, 1 )
VAR end_date =
    DATE ( YEAR ( TODAY () ) - 1, 12, 31 )
RETURN
    CALCULATE ( MAX ( Sales[Sales] ), DATESBETWEEN ( start_date, end_date, day ) )

If above DAX doesn’t work, please share some sample data and clarify desired results.

Regards,

Jimmy Tao

[SivaMani]

@diddlmus,

Could you please share sample data with the expected result?

[diddlmus]

@SivaMani

 
I hope this shows what i mean to do.