Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |