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,

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!