topic Re: How to turn multiple SQL joins into DAX? Example challenge in DAX Commands and Tips

How to turn multiple SQL joins into DAX? Example challenge

Anonymous — Fri, 21 Jan 2022 19:07:13 GMT

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_Year_Week ("202219", meaning ISO Year 2022, week 19), I want to show the sales aggregated for ISO year 2011, week 19.

Here is my SQL code:

SELECT

iso_week,
c.iso_year_week,
Sum(sold_quantity) AS Prior_Sales
FROM [sales] a
JOIN

(SELECT calendar_date, iso_year_week FROM [dim_date] WHERE current_fin_year = 'Previous') b
ON a.calendar_date = b.calendar_date
RIGHT JOIN

(SELECT DISTINCT iso_week, iso_year_week FROM [dim_date] WHERE current_fin_year = 'current') c

-- using right join because ISO week are sometimes 52, sometimes 53
ON a.iso_week = c.iso_week
GROUP BY iso_week,
c.iso_year_week
ORDER BY 2

Re: How to turn multiple SQL joins into DAX? Example challenge

ValtteriN — Fri, 21 Jan 2022 20:25:26 GMT

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!

Re: How to turn multiple SQL joins into DAX? Example challenge

Anonymous — Fri, 21 Jan 2022 20:29:00 GMT

Hi,

Thanks for your response and this is a great formula to know!

The issue here is that the requirement is to look at the same "ISO Weeks", which are usually not the same dates and it's what complicates the problem.

Re: How to turn multiple SQL joins into DAX? Example challenge

ValtteriN — Sat, 22 Jan 2022 13:08:28 GMT

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:

Re: How to turn multiple SQL joins into DAX? Example challenge

Anonymous — Mon, 24 Jan 2022 17:56:02 GMT

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?

Re: How to turn multiple SQL joins into DAX? Example challenge

ValtteriN — Mon, 24 Jan 2022 17:59:59 GMT

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.

Re: How to turn multiple SQL joins into DAX? Example challenge

Anonymous — Mon, 24 Jan 2022 19:17:58 GMT

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!