Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |