locked
Change in the value of the current period with the last non-blank period RRS feed

  • Question

  • Some time ago, I had to return the variation from the sale of the current day to the last sale made, in which it would not necessarily be the previous day always or before the previous day. I need help now.

     

     

    Sellers Customer Date Sale
    Beth WWW 22/04/2017  $ 3,97
    Beth WWW 07/05/2017  $ 3,12
    Beth WWW 07/01/2018  $ 1,41
    Paul PKZ 14/01/2017  $ 3,25
    Paul PKZ 02/04/2017  $ 2,97
    Paul PKZ 16/02/2018  $ 3,47
    Peter XYZ 08/01/2018  $ 3,45
    Peter XYZ 10/03/2018  $ 4,00
    Peter XYZ 22/03/2018  $ 3,81
    Peter XYZ 23/05/2018  $ 1,29

     

     

    I need to return the variation from the average sale of the month to the last month. Peter, for example, made a sale in May. And before it did two sales in March. How do I return the variation of these two periods in the DAX?



    Tuesday, July 31, 2018 2:05 PM

Answers

  • Hi Pietro Farias,

    Thanks for your question.

    >>>I need to return the variation from the average sale of the month to the last month. Peter, for example, made a sale in May. And before it did two sales in March. How do I return the variation of these two periods in the DAX?
    You can create calculated columns to achieve this. Please note that all of the DAX formula are created as calculated columns in the sample data provided by you:

    YearMonth =
    YEAR ( Sales[Date] ) * 100
        + MONTH ( Sales[Date] )
    
    
    AVGSALE =
    AVERAGEX (
        FILTER (
            Sales,
            Sales[Sellers] = EARLIER ( Sales[Sellers] )
                && Sales[YearMonth] = EARLIER ( Sales[YearMonth] )
        ),
        Sales[Sale]
    )
    
    
    PreviousMonth =
    MAXX (
        FILTER (
            Sales,
            Sales[Sellers] = EARLIER ( Sales[Sellers] )
                && Sales[YearMonth] < EARLIER ( Sales[YearMonth] )
        ),
        Sales[YearMonth]
    )
    
    
    LastMonthAvg =
    CALCULATE (
        MAX ( Sales[AVGSALE] ),
        FILTER (
            Sales,
            Sales[Sellers] = EARLIER ( Sales[Sellers] )
                && Sales[YearMonth] = EARLIER ( Sales[PreviousMonth] )
        )
    )
    
    
    variation =
    Sales[AVGSALE] - Sales[LastMonthAvg]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 1, 2018 2:52 AM

All replies

  • Somebody? :(
    Tuesday, July 31, 2018 8:02 PM
  • Hi Pietro Farias,

    Thanks for your question.

    >>>I need to return the variation from the average sale of the month to the last month. Peter, for example, made a sale in May. And before it did two sales in March. How do I return the variation of these two periods in the DAX?
    You can create calculated columns to achieve this. Please note that all of the DAX formula are created as calculated columns in the sample data provided by you:

    YearMonth =
    YEAR ( Sales[Date] ) * 100
        + MONTH ( Sales[Date] )
    
    
    AVGSALE =
    AVERAGEX (
        FILTER (
            Sales,
            Sales[Sellers] = EARLIER ( Sales[Sellers] )
                && Sales[YearMonth] = EARLIER ( Sales[YearMonth] )
        ),
        Sales[Sale]
    )
    
    
    PreviousMonth =
    MAXX (
        FILTER (
            Sales,
            Sales[Sellers] = EARLIER ( Sales[Sellers] )
                && Sales[YearMonth] < EARLIER ( Sales[YearMonth] )
        ),
        Sales[YearMonth]
    )
    
    
    LastMonthAvg =
    CALCULATE (
        MAX ( Sales[AVGSALE] ),
        FILTER (
            Sales,
            Sales[Sellers] = EARLIER ( Sales[Sellers] )
                && Sales[YearMonth] = EARLIER ( Sales[PreviousMonth] )
        )
    )
    
    
    variation =
    Sales[AVGSALE] - Sales[LastMonthAvg]


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 1, 2018 2:52 AM