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

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

1 ACCEPTED SOLUTION

Accepted Solutions
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.
3 REPLIES 3
Microsoft

## Re: Sum Sales Group by date

Hi @nannimora,

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

## Re: Sum Sales Group by date

thanks for helping me.

I see now all blank value, because the calculated column give me blank value. i dunno why

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.

Announcements

#### 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!

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

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