This is my DAX Query solution to Maven Analytics Data Drill #7 – Turning Bullish.
The challenge is to find the Golden Crosses in the dataset, and submit the most recent one, as a solution.
More information to the challenge can be found on their website.
Disclaimer: I did use Gen-AI (Microsoft Co-Pilot) in a part of this solution. Why ? Because DAX is just hard sometimes 😜🤷🏾♂️
I have solved this as a DAX query, then will visualise this in Power BI.
According to the instructions, we need to create a table. The table will have columns from the original dataset. It will also include additional columns for the evaluation.
Original Table Columns
- Date
- Closing Price
New Table Columns
- Date
- Closing Price
- 50-day moving average
- 200-day moving average
- Golden Cross
50-day moving average
First, let’s create a measure that refers to the Closing Price, so that we can use it, easily, in other measures.
DEFINE
MEASURE Box[Closing Price] = SUM( SPY_close_price_5Y[Close] )Then we create a measure that calculates the 50-Day Moving Average (MVA).
My initial approach involved using the time intelligence functions. I used DATEADD() to filter out 50 days for each date in the table. After that, I calculated the average.
MEASURE SPY_close_price_5Y[50_DAY_MVA] =
VAR _FirstDate = DATEADD(SPY_close_price_5Y[Date],-50,DAY)
VAR _LastDate = MAX(SPY_close_price_5Y[Date])
RETURN
CALCULATE(
AVERAGE(SPY_close_price_5Y[Close]),
_FirstDate <= SPY_close_price_5Y[Date] &&
_LastDate >= SPY_close_price_5Y[Date],
ALL(SPY_close_price_5Y[Date])
)The above code, gets the first date and the last date, relative to the filter context, then calculates the average, while removing all the filters. Once I have all the dates, then I just calculate the average.
Sample Result :
This seems correct, on the surface, as it resembles the closing price, so it could be the correct solution. However, it is not correct.
If we take the sum of the closing prices, divided by 50 days, inclusive, for 31/10/2025 , we get 662.172
Average for the past 50 days, all dates inclusive, for the 31st.
But why is on earth, is this happening ? It should be working, right ???!! Oh no, I just got a message from my Manager, she says, she needs the report ready for her meeting later this week. 😫
Calm, down, not to worry, this can be explained.
The problem, is we used DATEADD() to get the required dates. The nature of DATEADD() is to return a full-list of dates from the start date until the end date. This would be sufficient for most use cases, however, for this dataset, there happens to be missing dates. Therefore the expression is being evaluated by a date table with blanks in the closing price. This would be equivalent to the below, demonstrated in a spreadsheet.
If we go 50 days back, from 31/10/25, our start date would be 11/09/25. However, since we have set the expression to less or equal than 31/10/25, we get a count of 51.
Using DATEADD(), with 50 as the first parameter, and having less than or equal to, simulation.
We can still see the average doesn’t match the one we saw previously for the incorrect answer (667.65). This is because the DAX engine, removes the blanks rows from the dataset before calculating the average. This is okay. Blanks are empty values. They are not the same as having a 0 value. A 0 value would be added to the count, I believe. However, this is not what we want to happen for this use case.
Without blanks.
Debugging the incorrect value.
Now we know what is happening. We can proceed to correct the measure. The solution is to not use the DATEADD() function. This is where AI can come in and assist, to look for an alternative. As a DAX developer, this ideally how you can use Gen-AI in your workflow-using it in moderation. Once you understand the inner workings of the problem. Co-Pilot came up with the following solution:
MEASURE Box[50_DAY_MVA] =
VAR _LastDate = MAX( SPY_close_price_5Y[Date] )
VAR Last50Rows =
TOPN(
50,
FILTER(
ALL( SPY_close_price_5Y ),
SPY_close_price_5Y[Date] <= _LastDate
),
SPY_close_price_5Y[Date], DESC
)
RETURN
AVERAGEX( Last50Rows, SPY_close_price_5Y[Close] )Using TOPN() as table filter, is clever, as we will, now, only consider the available dates when calculating the average. What matters is that we get the correct count (50) and the correct sum (33,108.6).
The variable last date stores the maximum date of the table relative to the filter context. (Which, in this case, would just be the exact date being evaluated e.g 31/10/25). We remove all other filters using ALL(). Then, we get a table with the max date relative to the filter context using FILTER(). We then, use TOPN() to get the first 50 rows, sorted by descending order a.k.a the last 50 entries.
Finally, we return the average closing price using AVERAGEX() which iterates over our new table.
Correct value.
Using this same logic we can find the 200-day moving average as well.
200-day moving average
MEASURE Box[200_DAY_MVA] =
VAR _LastDate = MAX( SPY_close_price_5Y[Date] )
VAR Last200Rows =
TOPN(
200,
FILTER(
ALL( SPY_close_price_5Y ),
SPY_close_price_5Y[Date] <= _LastDate
),
SPY_close_price_5Y[Date], DESC
)
RETURN
AVERAGEX( Last200Rows, SPY_close_price_5Y[Close] )The only difference here is we have changed the first parameter of the TOPN() function to 200. We also changed the variable names.
Sample:
Let’s check this in Excel:
Seems to be correct
Golden Cross
Now to evaluate for the golden cross, in a tabular manner, we need to check for two things
- If today’s (i.e. the current date) 50-day MVA is greater than today’s 200-day MVA.
- If yesterday’s 50-day MVA was less than today’s 200-day MVA.
For the first part , we can already compute this with our defined measures. However, for the second part, we still need to create the measures for the previous day
EVALUATE
---Summarised Table---
VAR T =
SUMMARIZECOLUMNS(
SPY_close_price_5Y[Date],
"Closing Price", [Closing Price],
"50_DAY_MVA", [50_DAY_MVA],
"200_DAY_MVA", [200_DAY_MVA]
)
---Adding Previous Day's 50 day MVA---
VAR Y =
ADDCOLUMNS(T,
"prev_50_DAY_MVA", CALCULATE (
[50_DAY_MVA],
OFFSET (
-1,
T,
ORDERBY ( SPY_close_price_5Y[Date], ASC )
)
),
---Adding Previous Day's 200 day MVA---
"prev_200_DAY_MVA", CALCULATE (
[200_DAY_MVA],
OFFSET (
-1,
T,
ORDERBY ( SPY_close_price_5Y[Date], ASC )
)
)
)
Here we are offsetting the dates by -1, to get the previous days data. We use the OFFSET() function, as a filter argument to calculate.
We then finish off by adding the Golden cross logic check and return a 1 for TRUE, and 0 for FALSE. To get the exact days, we filter the ones.
---Calculating Golden Cross---
VAR X =
ADDCOLUMNS(
Y,
"Golden Cross", IF(
[50_DAY_MVA] > [200_DAY_MVA]
&&
[prev_50_DAY_MVA] < [prev_200_DAY_MVA],
1,0
)
)
RETURN
---Querying only Golden Cross Dates---
FILTER( X, [Golden Cross] = 1 )
ORDER BY
SPY_close_price_5Y[Date] DESC
Golden Cross Dates
Let’s check to see if we are correct.
Nice ! 🙂😄🎉
Visualising this in Power BI
Line Chart showing Golden Crosses.
Line Chart showing Golden Crosses, zoomed.
Solution in video format
That’s all for today! 👋🏾😁
