Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to turn multiple SQL joins into DAX? Example challenge

Help will be very much appreciated!   Say if I want to create a measure called "Prior_Sales", now I have two tables I can use - [dim_date] and [sales]. The result should be: given a particular ISO...
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi,

    In the case of comparing weeks you could use variables.
    If your data format is like you described somethin like this should do:
    e.g. [measure] =

    var _ISOWEEK = MAX(Isoweeks[ISOWeek])
    var _LY_ISOWEEK = CONCATENATE(LEFT(_ISOWEEK,4)-1,RIGHT(_ISOWEEK,2))
    return

    CALCULATE(SUM(Isoweeks[Value]),ALL(Isoweeks),Isoweeks[ISOWEEK]=_LY_ISOWEEK)

    Example data:


    End result:

     



ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

Hi,

For calculating previous years values you can use SAMEPERIODLASTYEAR


[Sales LY]=

CALCULATE([Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!


Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Valterri, I just tried this formula and encountered a strange error prompt "an invalid numeric representation of a date value was encountered", do you happen to know what does it mean?

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    I would check whether or not you have NULL values and also what is the data format of your columns. These might be the reason for this error.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi, thank you again. I guess u r right there might be something with the original data. I don't see any null value or data type issue but when I reimported the original date data it was solved. Thank you very much again!