Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nannimora
Helper I
Helper I

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.

 

Cattura.PNG

 

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:

Cattura2.PNG

 

i do not undestand where he takes 1.431.078.25.

 

Pls help me.

 

Thanks

 

Giovanni Morabito

1 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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.