Forum Discussion
Sum Sales Group by date
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
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
3 Replies
- v-yulgu-msft
Microsoft Employee
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
Helper I
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
Microsoft Employee
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