cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Microsoft v-yulgu-msft
Microsoft

Re: Sum Sales Group by date

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
Microsoft v-yulgu-msft
Microsoft

Re: Sum Sales Group by date

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.
nannimora Helper I
Helper I

Re: Sum Sales Group by date

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

 

 

Microsoft v-yulgu-msft
Microsoft

Re: Sum Sales Group by date

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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors