Sum Sales Group by date

[nannimora]

Hi everyone, 

i have a problem with my dax formula:

I'll try to explain and sorry for my bad english.

I have to sum the sales of one month and compare with the same month of the previous year. the problem that i have at this moment is that when a data of the last year doesnt match with the same data of this year i have a blank resoult but i would like to have the same resoult of the previous day.

For Example: in for the date 16/04/2018 in the second coloumn instead of the blank value i would like to have 612997.66 the same of the previous day. the first value available. 

at this moment i use this dax formula:

Sales Last Year2 = CALCULATE(SUM(VENDITE[Valore]);DATESMTD(SAMEPERIODLASTYEAR(VENDITE[DATA_V])))

i tried to modify with this dax

Sales Last Year = IF(
            ISBLANK(CALCULATE(SUM(VENDITE[Valore]);DATESMTD(SAMEPERIODLASTYEAR(VENDITE[DATA_V])))
                );
            CALCULATE(SUM(VENDITE[Valore]);DATESMTD(SAMEPERIODLASTYEAR(VENDITE[DATA_V]-1))
                );
            CALCULATE(SUM(VENDITE[Valore]);DATESMTD(SAMEPERIODLASTYEAR(VENDITE[DATA_V])))
)

But the resoult is this:

i do not undestand where he takes 1.431.078.25.

Pls help me.

Thanks

Giovanni Morabito

[v-yulgu-msft]

Hi @nannimora,

Please try this:

New a calculated column:

Sales Last Year2 = CALCULATE(SUM(VENDITE[Valore]);DATESMTD(SAMEPERIODLASTYEAR(VENDITE[DATA_V])))

New a measure:

Sales Last Year =
CALCULATE (
    LASTNONBLANK ( VENDITE[Sales Last Year2], 1 ),
    FILTER ( ALL ( VENDITE ), VENDITE[DATA_V] <= SELECTEDVALUE ( VENDITE[DATA_V] ) )
)

Add above measure to table visual.

Regards,

Yuliana Gu

[nannimora]

hi @v-yulgu-msft,

thanks for helping me.

but your solution doesn't work.

I see now all blank value, because the calculated column give me blank value. i dunno why

[v-yulgu-msft · Accepted solution]

Hi @nannimora,

To calculate the MTD in previous year via calculated column, you should modify the DAX formula to:

Sales Last Year Col =
CALCULATE (
    CALCULATE ( SUM ( VENDITE[Valore] ), ALLEXCEPT ( VENDITE, VENDITE[DATA_V] ) ),
    DATESMTD ( SAMEPERIODLASTYEAR ( VENDITE[DATA_V] ) )
)

Next step, for measure, please refer to the formula provided in my original post.

Best regards,

Yuliana Gu