Forum Discussion

nannimora's avatar
nannimora
Icon for Helper I rankHelper I
8 years ago
Solved

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

  • 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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft 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

    • nannimora's avatar
      nannimora
      Icon for Helper I rankHelper I

      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

       

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee

        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