How to calculate Same Day last year sales & Same day, same time last year sales

[GauthamBR]

I need to calculate - Same Day last year sales & Same day, same time last year sales. I don't want to use SAMEPERIOD DAX function because my clients day counting is different. Attaching an image for reference.

First I need to set FLAG as Y or N depending on whether the store had sales on same day last year and also I need to calculate the sales amount for those with Y.

Thanks in advance!

[Zubair_Muhammad · Accepted solution]

@GauthamBR

For flagging try this column

Flag Column =
VAR CurrentYear = YEAR ( TODAY () )
VAR CurrentRowDate = TableB[Date]
VAR check =
    CALCULATE (
        COUNTROWS ( TableB ),
        FILTER (
            ALLEXCEPT ( TableB, TableB[StoreID] ),
            TableB[Date]
                = DATE ( YEAR ( CurrentRowDate ) - 1, MONTH ( CurrentRowDate ), DAY ( CurrentRowDate ) )
        )
    )
RETURN
    IF (
        YEAR ( CurrentRowDate ) = CurrentYear
            && Check > 0,
        "Y",
        IF ( YEAR ( CurrentRowDate ) = CurrentYear, "N" )
    )

[v-piga-msft · Accepted solution]

Hi @GauthamBR,

For Table A, you could try with the fomula below which I have modified from @Zubair_Muhammad.

LastYearSales = 
VAR CurrentYear =
    YEAR ( TODAY () )
VAR CurrentRowDate = 'TableA'[Date]
RETURN
    IF (
        TableA[Flag] = "Y",
        CALCULATE (
            max ( TableA[Sales] ),
            FILTER (
                 ALL(TableA),
                TableA[Date]
                    = DATE ( YEAR ( CurrentRowDate ) - 1, MONTH ( CurrentRowDate ), DAY ( CurrentRowDate ) )
            )
        )
    )

Here is the result.

For the table B, I still have a little confused about the LastYr Sales, why the result is 10 and 0. What is the logic?

Please describe your scenario in more details.

Best Regards,

Cherry

[GauthamBR]

Thanks much for the help @Zubair_Muhammad and @v-piga-msft !

@v-piga-msft Table B's updated screenshot is attached

The Flagging concept is similar to Table A. But LastYearSales needs to be calculated based on " Same day - Same time - last year"

[v-piga-msft]

Hi @GauthamBR,

The formula from @Zubair_Muhammad could help you get the correct result for Table B.

Best  Regards,

Cherry

[Zubair_Muhammad · Accepted solution]

@GauthamBR

For flagging try this column

Flag Column =
VAR CurrentYear = YEAR ( TODAY () )
VAR CurrentRowDate = TableB[Date]
VAR check =
    CALCULATE (
        COUNTROWS ( TableB ),
        FILTER (
            ALLEXCEPT ( TableB, TableB[StoreID] ),
            TableB[Date]
                = DATE ( YEAR ( CurrentRowDate ) - 1, MONTH ( CurrentRowDate ), DAY ( CurrentRowDate ) )
        )
    )
RETURN
    IF (
        YEAR ( CurrentRowDate ) = CurrentYear
            && Check > 0,
        "Y",
        IF ( YEAR ( CurrentRowDate ) = CurrentYear, "N" )
    )

[Zubair_Muhammad]

@GauthamBR

Then Lastyearsales

LastYearSales =
VAR CurrentYear =
    YEAR ( TODAY () )
VAR CurrentRowDate = TableB[Date]
RETURN
    IF (
        TableB[Flag] = "Y",
        CALCULATE (
            SUM ( TableB[Sales] ),
            FILTER (
                ALLEXCEPT ( TableB, TableB[StoreID], TableB[Hour] ),
                TableB[Date]
                    = DATE ( YEAR ( CurrentRowDate ) - 1, MONTH ( CurrentRowDate ), DAY ( CurrentRowDate ) )
            )
        )
    )