Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DanPell
Frequent Visitor

Trouble with DAX formula to get previous campaign sales data

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!
  DanPell_0-1722845465950.png

 

1 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
    )
)

View solution in original post

12 REPLIES 12
Gabry
Super User
Super User

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
Frequent Visitor

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

bhanu_gautam
Super User
Super User

@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
        )
    )



Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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 🙂

Provide the pbix with sample data

DanPell
Frequent Visitor

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

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
Frequent Visitor

Thanks @Gabry I'll give it a go.

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

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

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_0-1722902478586.png

 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors