Trouble with DAX formula to get previous campaign sales data

[DanPell]

Hi there

I'm having trouble with my dax formula to retrieve previous campaign sales data.  I have sales volume for the current campaign per below which filters out any sales with tree type as "I".  This works perfectly. The slicer on my page refers to 'Winter Sales' which is the current campaign.

Product Sales Volume =

        if(ISBLANK(

            CALCULATE(

            SUM('Product Sales'[Quantity]),

            FILTER('Product Sales','Product Sales'[Tree Type] <> "I"))),

            "0",

            CALCULATE(

            SUM('Product Sales'[Quantity]),

            FILTER('Product Sales','Product Sales'[Tree Type] <> "I"))

        )

However when I try to calculate the previous campaign sales (being 'mid-year sale' campaign) I keep getting the same volume as the Winter Sale. 

This is my formula (note I have numbered the campaigns so Winter sale is number 5 and midyear sale is 4 - this is referred below as Previous Campaign Number)

Product Sales Volume (Previous Campaign) =

    CALCULATE(

        SUMX('Product Sales',[Product Sales Volume]),

        filter('Product Sales',SELECTEDVALUE('Product Sales'[Previous Campaign Number])

        )

The result of the above only shows the Winter sale number not the previous campaign sales numbers.  I have even tried to create a calculated column referring to the previous campaign and number and no luck.  Please help! Thanks!

  

[Gabry]

Hello buddy,

i don't undertsand why did you use a sumx?

Couldn't you just write:

calculate([Product Sales Volume], numberofcampain= selectedvalue(numberofcampain)-1).

Why use filter inside calculate? Also why use sumx?

Also in this formula:

Product Sales Volume =

        if(ISBLANK(

            CALCULATE(

            SUM('Product Sales'[Quantity]),

            FILTER('Product Sales','Product Sales'[Tree Type] <> "I"))),

            "0",

            CALCULATE(

            SUM('Product Sales'[Quantity]),

            FILTER('Product Sales','Product Sales'[Tree Type] <> "I"))

        )

it's better if you use variables and avoid filter inside calculate.
Also not sure why to check if a sum isblank

Product Sales Volume =
 var _sales= CALCULATE(  SUM('Product Sales'[Quantity]),'Product Sales'[Tree Type] <> "I")
return
if(ISBLANK(_sales),  "0",   _sales)

[DanPell]

Hi Gabry thanks I'll clean it up. Makes sense 

[bhanu_gautam]

@DanPell , You can update the measure as 

Product Sales Volume (Previous Campaign) =

    CALCULATE(

        SUM('Product Sales'[Quantity]),

        FILTER(

            'Product Sales',

            'Product Sales'[Tree Type] <> "I" &&

            'Product Sales'[Campaign Number] = SELECTEDVALUE('Product Sales'[Campaign Number]) - 1

        )

    )

[DanPell]

Hi @bhanu_gautam @Gabry I have also tried the following variation of the formula and I get a blank result (note the Campaign Name slicer on or off doesn't change the result):

Product Sales Volume (Previous Campaign) =

VAR PreviousCampNo = SELECTEDVALUE('Product Sales'[Campaign Number])-1

RETURN

CALCULATE(

    sum('Product Sales'[Quantity]),

    filter('Product Sales',

    'Product Sales'[Tree Type] <> "I" &&

    'Product Sales'[Campaign Number] = PreviousCampNo

    )

)

I've tried hardcoding the SELECTEDVALUE('Product Sales'[Campaign Number])-1 formula to just number "4" (see below) and it works when I turn off the Campaign Name slicer.  I need the slicer on though.  

Product Sales Volume (Previous Campaign) =

VAR PreviousCampNo = SELECTEDVALUE('Product Sales'[Campaign Number])-1

RETURN

CALCULATE(

    sum('Product Sales'[Quantity]),

    filter('Product Sales',

    'Product Sales'[Tree Type] <> "I" &&

    'Product Sales'[Campaign Number] = 4

    )

)

Just can't get this work! I am fairly new to PBI 🙂

[Gabry]

Provide the pbix with sample data

[DanPell]

Not sure how to share a sample file with all the workplace firewalls etc.  

[Gabry · Accepted solution]

Ok not sure what's your issue, hard to say like this, but try:

Product Sales Volume (Previous Campaign) =

VAR PreviousCampNo =max('Product Sales'[Campaign Number])-1

RETURN

CALCULATE(

    sum('Product Sales'[Quantity]),

    filter('Product Sales',

    'Product Sales'[Tree Type] <> "I" &&

    'Product Sales'[Campaign Number] = PreviousCampNo

    )

)

[DanPell]

Thanks @Gabry I'll give it a go.

[v-junyant-msft]

Hi @DanPell ,

May I ask if @Gabry 's reply solves your problem or helps you? If it helps, please accept @Gabry 's reply as solution so that more people can find the solution faster. Thank you!

Best Regards,
Dino Tao

[DanPell]

Thanks Dino - it didn't quite work but I have managed a workaround many thanks!

[DanPell]

Hi @bhanu_gautam I've tried the above and it doesn't seem to work.  I'm wondering if the campaign name slicer (which is implicity linked to the Campaign Number) on the page maybe affecting the outcome. I get a "blank" result.  The slicer I have is Campaign name being the current Winter Sale which is campaign number 5.  

Below is the data that the formula is pulling from.  Quantity is on a separate column not shown here.  Thanks let me know what else you need

[DanPell]

Thanks @bhanu_gautam let me try that and see if it works. Thanks