Central Tendencies

Blog

  • Maven Analytics – Data Drills #5 – Movie Metrics – DAX Query Solution

    This is my DAX Query solution to Maven Analytics Data Drill #5 – Movie Metrics.

    The challenge is to create new columns in the USERS table based on data given in the USER ACTIVITY table. The new columns to be added are :

    • Date from the first movie they finished
    • Name of the first movie they finished
    • Date from the last movie they finished
    • Name of the last movie they finished
    • Movies started
    • Movies finished

    Note: You might notice, I didn’t follow the instructions to the T . I did not create columns in the USERS table. Instead, I decided to solve this using a series of measures. This approach was natural for me, as I new the final query would produce the final table. Also, I completely missed the point where it should have been solved in the USERS table !

    First we create the Count Movies metric which as the name implies, gives as a count of the movies. This may help us with troubleshooting DAX code.

    DEFINE
    MEASURE 'Box'[Count_movies] =
            CALCULATE(
   COUNTROWS(    VALUES(
    'activity'[movie_name]
))

    Then we find the first movie the user has finished by getting the minimum date using the MIN() function, followed by the CALCULATE() function. The CALCULATE() function will force a context transition for each user. We then filter the activity table to only query finished movies.

    MEASURE 'Box'[First_Date] = 
        CALCULATE(
            MIN('activity'[date]),
            'activity'[finished] = 1
        )

    For First Name , we need to filter the table, by the minimum date and the finished flag. We access the min_date by referring to the first_date measure and store it in a variable. Thereafter we use it to filter the table and get the movie name by using the MAX() function.

    MEASURE 'Box'[First_Name] = 
        VAR min_date = [First_Date]
        RETURN
            CALCULATE(
                MAX('activity'[movie_name]),
                'activity'[date] = min_date,
                'activity'[finished] = 1
            )

    For Last Date and Last Name, the logic is similar to First Date and First Name but we change the MIN() to a MAX() function.

    MEASURE 'Box'[Last_Date] = 
        CALCULATE(
            MAX('activity'[date]),
            'activity'[finished] = 1
            
        )
        MEASURE 'Box'[Last_Name] = 
        VAR max_date = [Last_Date]
        RETURN
            CALCULATE(
                MAX('activity'[movie_name]),
                'activity'[date] = max_date,
                'activity'[finished] = 1
            )

    For the final two measures, Movies Started and Movies Finished, we use the COUNTROWS() function to count the rows in the table while filtering it by 0 or 1 respectively, while wrapping it around a calculate function.

    MEASURE 'Box'[Movies_Started] = 
                CALCULATE(
                    COUNTROWS('activity'),
                    'activity'[finished] = 0
                )
MEASURE 'Box'[Movies_Finished] = 
                CALCULATE(
                    COUNTROWS('activity'),
                    'activity'[finished] = 1
                )

    Finally, after creating the measures, we can create the table and pass the measures as columns in the USER ACTIVITY table. We filter the table using the FILTER() function for the “Fight Club” movie in the last name column, to get users who watched the movie last. To get the final answer, we count the rows in the table using COUNTROWS().

    (Note: If we were to pass them in the USERS table we would most likely have to use the RELATEDTABLE() function to refer to the columns in the other table.)

    EVALUATE
VAR T =
SUMMARIZECOLUMNS(
    'activity'[user_id],
    "Count_movies", [Count_movies],
    "First_Date", [First_Date],
    "First_Name", [First_Name],
    "Last_Date", [Last_Date],
    "Last_Name", [Last_Name],
    "Movies_Started", [Movies_Started],
    "Movies_Finished", [Movies_Finished]
)
 VAR Y = FILTER(T, [Last_Name] = "Fight Club")
RETURN
	// T
	// V
   { COUNTROWS( Y ) }
ORDER BY
    [Count_movies] DESC

    Summarised Table

    Final Table

    Final Count

    Submitting the answer

    That’s all for today ! 🤗👋🏾

    Video Tutorial

  • Maven Analytics – Data Drills #4 – Spot the Sale – DAX Query Solution

    This is my DAX Query solution to Maven Analytics Data Drill #4 – Spot the Sale.

    The challenge is to join each transaction to the promotion active on its order date, and find out how many orders were placed outside of the promotional period.

    Disclaimer: This solution is based Aaron Perry’s DAX solution, an instructor from Maven Analytics.

    For this challenge, my initial impulse was to unpivot the promotions table, so that the dates can all fall under one column. Once we have them under one column, then we can find a way to build a table that contains the correct promotion for each date, then perform a left join on the orders table.

    Suggested Solution Example:

    Promotions table

    promo_idpromo_nameDates
    BF_2023Black Friday24/11/2023
    BF_2023Black Friday25/11/2023
    BF_2023Black Friday26/11/2023
    BF_2023Black Friday27/11/2023
    BF_2023Black Friday28/11/2023
    BF_2023Black Friday29/11/2023

    Orders Table after left join

    order_idorder_dateorder_quantitypromo_id
    61024-Nov-232BF_2023
    61125-Nov-235BF_2023
    61225-Nov-233BF_2023
    61327-Nov-231BF_2023
    61428-Nov-232BF_2023
    61529-Nov-235BF_2023
    61629-Nov-232BF_2023

    However, I feel, the challenge involves how to accurately and dynamically expand the promotions table for each promotion date.

    This approach, although may work, is still an SQL way of thinking. The better approach is to think in DAX. The better way is to create a new column that would check for each row, in the column if it belongs in the promotion range, then return the promotion name.

    DEFINE
    COLUMN 'orders'[promo_ids] = 
        VAR d = 'orders'[order_date]
    RETURN
        CALCULATE(
            MAX('promotions'[promo_id]),
            FILTER(
                ALL('promotions'),
                'promotions'[start_date] <= d &&
                d <= 'promotions'[end_date]
            )
        )
EVALUATE
{
    COUNTROWS(
        FILTER( 'orders', ISBLANK( 'orders'[promo_ids] ) )
    )
}

    First, the current date being evaluated , in the row context and then saved in the variable d. We then remove all of the filters from the promotions table using ALL(), then explicitly filter the promotions table again, based on the condition that the order date is between the start date and end date. once this is true it returns a one row table (since there is no overlap in promotions). Thereafter we get the individual value using max and wrap it around calculate, in order to force the filter.

    Once we have the column, we then query the orders table for blank promo ids.

    Video Walkthrough

    That’s all for today! 👋🏾😁

  • Maven Analytics – Data Drills #3- Rolling Up, Looking Back – DAX Query Solution

    Maven Analytics – Data Drills #3- Rolling Up, Looking Back – DAX Query Solution

    This is my DAX Query solution to Maven Analytics Data Drill #3 – Rolling Up, Looking Back.

    The challenge is to find the difference in MoM sales values from April to May for the Astoria location.

    More information to the challenge can be found on their website.

    For this Data Drill the request and solution is relatively simple in nature, as any BI analyst, in any capacity, will encounter some form of Month-on-Month time intelligence request.

    The solution is as follows:

    First, we define our measures. Total Sales, Previous Month Sales and the Abs Sales (actually just values, not the mathematical absolute of anything).

    Total Sales is just the sum of sales.

    PM Total Sales (Previous Month Total Sales) uses the CALCULATE() and the DATEADD() function, The expression to be evaluated is Total Sales, but going back one-month exactly, for each date in the table. e.g. 15-Feb-25 would now be 15-Jan-25.

    Abs_Sales (Absolute Sales) finds the difference between the current sales and the previous month sales.

    /*-------------Maven Analytics - Data Drills-------------*/

/*----Data Drills - 3 - Rolling Up, Looking Back----*/


DEFINE
    MEASURE Box[Total Sales] = SUM( Coffee[sales] )
    MEASURE Box[PM Total Sales] =
        CALCULATE(
            [Total Sales],
            DATEADD( Calendar[Date], -1, MONTH )
        )
    MEASURE Box[Abs_Sales] = [Total Sales] - [PM Total Sales DA]

    Thereafter, we now need to query the data, grouping by Year-Month, and Store.

    To minimise the number of rows, we filter the output query to get only the months in question, as well as the store.

    
EVALUATE
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        Calendar[Year-Month],
        Coffee[store],
        "Total Sales", [Total Sales],
        "PM Total Sales DA", [PM Total Sales DA],
        "Abs Sales", [Abs_Sales]
    ),
    Calendar[Date] >= DATE( 2023, 4, 1 ),
    Calendar[Date] < DATE( 2023, 6, 1 ),
    Coffee[store] = "Astoria"
)
ORDER BY 
    Calendar[Year-Month], 
    Coffee[store]

    Query Result

    Submitting this on the website

    Video Walk-through

  • Maven Analytics – Data Drills #7- Turning Bullish – DAX Query Solution

    Maven Analytics – Data Drills #7- Turning Bullish – DAX Query Solution

    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

    1. Date
    2. Closing Price

    New Table Columns

    1. Date
    2. Closing Price
    3. 50-day moving average
    4. 200-day moving average
    5. 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 date at the row level. 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

    1. If today’s (i.e. the current date) 50-day MVA is greater than today’s 200-day MVA.
    2. 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.

    Video Tutorial

    That’s all for today! 👋🏾😁

  • What is Business Intelligence ?

    What is Business Intelligence ?

    Photo by Christina Morillo from Pexels.

    Companies, big and small, often face many challenges when operating in their business environments. Challenges that require informed decision-making in order to address them correctly. For example, A firm may face declining sales in a certain region, however, this may not be apparent due to a lack of clear visibility; or a competitor may have raised their prices on a premium product, causing a business to miss potential revenue; or increased delays in transportation in its supply chain.

    Enter Business Intelligence, usually abbreviated as B.I

    Business Intelligence is the process of using data to answer business questions in order to produce a call to action.

    Management, Team leaders and team members, need to have sufficient information before taking any course of action, otherwise they risk negatively impacting the business. Luckily, in today’s world, businesses generate large amounts of data (Big Data), which can be used to gain insights before making a decision.

    The B.I process involves identifying the business requirements, data sources, transforming said data, discovering insights, visualising, and finally presenting and sharing the findings with stakeholders, in order for them to take action.

    B.I often falls under Descriptive and Diagnostic analytics – B.I helps us know “What happened ?” and “Why did it happen ?”. In that sense, it is a backward-looking or historical form of analytics.

    It is often real-time or near real time which allows for stakeholders to react quickly to any changes in the internal or external environment.

    HR Attrition Dashboard by Pradeep Kumar. Interactive version

    Some benefits of B.I include

    • Improved reaction time towards business events.
    • Proactive (preventative) measures before business events happen.
    • Performance overview of the business (usually in the form of KPI’s).
    • Enhanced operational efficiencies.

    In order for companies to maximise the benefits of business intelligence, they need to have a well thought out B.I Strategy which may fall under a companies overall (Big) data strategy.

Disclaimer - Privacy Policy