Forum Discussion

Valentb's avatar
Valentb
Advocate I
2 years ago
Solved

Compare same campaign - Different dates in Previous Year Using DAX

Hi Guys,

 

I'm trying to solve a puzzle.

I have a date_dim that contain dates and campaign name.
Since the campaigns both can have variable dates but needs to be the same 'campaign name -1 year'


I cannot use sameperiod last year, but maybe date add and some variable?
I'm just really stuck and have no clue how to solve this.

Thanks alot for any help


Need Help
  • Valentb's avatar
    Valentb
    2 years ago

    Edited: Got it working. added Convert ((...), String)

    So far have the following code, but it's not working when i insert -1 it no longer works.
    Tried adding "Value" - doesn't help




    I'm close - also it's direct query in dual mode so no calculated column is allowed

     

    Var last_campaign = (LEFT(SELECTEDVALUE(Date[Campagin_Name]),3)&" "& Value(RIGHT(SELECTEDVALUE(Date[Campagin_Name]),4)-1))

return

CALCULATE([Sales],
REMOVEFILTERS(Date),
Filter(Date,Values(Date[Campagin_Name])=last_campaign))

     

     

3 Replies

  • mickey64's avatar
    mickey64
    Super User
    2 years ago

    For your reference.

     

    I added 'Campaign_Name-1' column in the table.

    You can select 2 campaigns with 2 slicers.

     

     

  • VijayP's avatar
    VijayP
    Community Champion
    2 years ago

    Valentb 

    Try using DateADD, just attempt and if you have challenge paste the current result with expected result

  • Valentb's avatar
    Valentb
    Advocate I
    2 years ago

    Edited: Got it working. added Convert ((...), String)

    So far have the following code, but it's not working when i insert -1 it no longer works.
    Tried adding "Value" - doesn't help




    I'm close - also it's direct query in dual mode so no calculated column is allowed

     

    Var last_campaign = (LEFT(SELECTEDVALUE(Date[Campagin_Name]),3)&" "& Value(RIGHT(SELECTEDVALUE(Date[Campagin_Name]),4)-1))

return

CALCULATE([Sales],
REMOVEFILTERS(Date),
Filter(Date,Values(Date[Campagin_Name])=last_campaign))