complex real-life DAX examples

 Here are some complex real-life DAX examples:

1. Cumulative Total with Filter: Calculate a cumulative total for a specific category while ignoring others. For instance, finding the cumulative sales for a particular product category over time.
   Cumulative Sales = CALCULATE( SUM('Sales'[SalesAmount]), FILTER( ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) ), 'Product'[Category] = "Electronics" )
2. YTD (Year-to-Date) with Previous Year Comparison: Calculate Year-to-Date sales and compare them to the same period in the previous year.  
   YTD Sales = TOTALYTD(SUM('Sales'[SalesAmount]), 'Date'[Date], ALL('Date')) YTD Sales LY = CALCULATE([YTD Sales], SAMEPERIODLASTYEAR('Date'[Date]))
3. Top N Items by Sales: Determine the top N items by sales, dynamically letting users choose N via a slicer. 
   Top N Items = RANKX(ALL('Product'), [Total Sales], , DESC, Dense) Top N Items Filtered = FILTER('Product', [Top N Items] <= SELECTEDVALUE('Slicer'[N]))
4. Moving Average with Offset: Calculate a moving average with a variable offset, allowing users to specify the number of periods to include in the average. 
   Moving Avg = VAR SelectedOffset = SELECTEDVALUE('Slicer'[Offset]) RETURN AVERAGEX( FILTER(ALL('Date'), 'Date'[Date] <= MAX('Date'[Date])), CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -SelectedOffset, DAY)) )
5. ABC Analysis: Categorize products into A, B, and C classes based on their contribution to total sales (e.g., A items contribute to 80% of sales). 
   ABC Classification = VAR TotalSales = SUMX(ALL('Product'), [Total Sales]) VAR CurrentProductSales = [Total Sales] VAR PercentContribution = CurrentProductSales / TotalSales RETURN IF(PercentContribution >= 0.8, "A", IF(PercentContribution >= 0.15, "B", "C"))
6. Complex example:

Sales Performance Analysis = 

VAR CurrentYear = YEAR(TODAY())

VAR PreviousYear = CurrentYear - 1

VAR CurrentMonth = MONTH(TODAY())

VAR PreviousMonth = IF(CurrentMonth = 1, 12, CurrentMonth - 1)

VAR StartDate_CurrentYear = DATE(CurrentYear, 1, 1)

VAR EndDate_CurrentYear = TODAY()

VAR StartDate_PreviousYear = DATE(PreviousYear, 1, 1)

VAR EndDate_PreviousYear = DATE(PreviousYear, 12, 31)

RETURN

    SUMMARIZECOLUMNS(

        'Products'[Category],

        "Total Sales Current Year", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] >= StartDate_CurrentYear && 'Date'[Date] <= EndDate_CurrentYear),

        "Total Sales Previous Year", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] >= StartDate_PreviousYear && 'Date'[Date] <= EndDate_PreviousYear),

        "Growth %", DIVIDE(([Total Sales Current Year] - [Total Sales Previous Year]), [Total Sales Previous Year], 0)

    )

explanation:

We define variables for the current year, previous year, current month, and previous month to make our calculations dynamic.

We calculate the start and end dates for the current and previous years based on the current date.

We use SUMMARIZECOLUMNS to create a summary table with the following columns:

'Products'[Category]: Category of the product.

"Total Sales Current Year": Total sales for the current year using CALCULATE with date range filtering.

"Total Sales Previous Year": Total sales for the previous year using CALCULATE with date range filtering.

"Growth %": Percentage growth in sales from the previous year to the current year.

       7.Another example

Sales Performance Analysis = 

VAR CurrentYear = YEAR(TODAY())

VAR PreviousYear = CurrentYear - 1

VAR StartDate_CurrentYear = DATE(CurrentYear, 1, 1)

VAR EndDate_CurrentYear = TODAY()

VAR StartDate_PreviousYear = DATE(PreviousYear, 1, 1)

VAR EndDate_PreviousYear = DATE(PreviousYear, 12, 31)

RETURN

    ADDCOLUMNS(

        FILTER(

            ALL('Products'[Category]),

            NOT ISBLANK([Total Sales Current Year]) || NOT ISBLANK([Total Sales Previous Year])

        ),

        "Category", 'Products'[Category],

        "Total Sales Current Year", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] >= StartDate_CurrentYear && 'Date'[Date] <= EndDate_CurrentYear),

        "Total Sales Previous Year", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] >= StartDate_PreviousYear && 'Date'[Date] <= EndDate_PreviousYear),

        "Growth %", DIVIDE(([Total Sales Current Year] - [Total Sales Previous Year]), [Total Sales Previous Year], 0),

        "Growth Status", IF([Total Sales Current Year] > [Total Sales Previous Year], "Positive", IF([Total Sales Current Year] < [Total Sales Previous Year], "Negative", "No Change"))

    )

explanation:

We define variables for the current year and previous year, as well as start and end dates for both years.

We use ADDCOLUMNS to create a new table with additional calculated columns.

We use FILTER to iterate over all product categories, removing any categories with no sales data for both the current and previous years.

We add a column for "Category" to identify the product category.

We calculate "Total Sales Current Year" and "Total Sales Previous Year" for each product category.

We calculate the "Growth %" for each product category by comparing sales figures between the current and previous years.

We add a column for "Growth Status" to indicate whether the sales growth is positive, negative, or unchanged compared to the previous year.

        8. using parameter:

Sales by Selected Category = 

VAR SelectedCategory = SELECTEDVALUE('Products'[Category])

RETURN

    CALCULATE(

        SUM('Sales'[Amount]),

        'Products'[Category] = SelectedCategory

    )

We define a variable SelectedCategory to store the value of the selected category from the parameter.

We use the SELECTEDVALUE function to retrieve the selected category from the 'Products' table. If the user has not selected a category, or if multiple categories are selected, SELECTEDVALUE will return BLANK().

We use CALCULATE to apply a filter to the 'Sales' table, restricting it to rows where the 'Category' column matches the selected category.

The query returns the total sales amount for the selected category.

        9.more complex DAX query that utilizes multiple tables, measures, and calculations to perform analysis on sales data:

Sales Analysis = 

VAR CurrentYear = YEAR(TODAY())

VAR PreviousYear = CurrentYear - 1

VAR StartDate_CurrentYear = DATE(CurrentYear, 1, 1)

VAR EndDate_CurrentYear = TODAY()

VAR StartDate_PreviousYear = DATE(PreviousYear, 1, 1)

VAR EndDate_PreviousYear = DATE(PreviousYear, 12, 31)

RETURN

    ADDCOLUMNS(

        CROSSJOIN(

            VALUES('Products'[Category]),

            VALUES('Customers'[Region])

        ),

        "Category", 'Products'[Category],

        "Region", 'Customers'[Region],

        "Total Sales Current Year", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] >= StartDate_CurrentYear && 'Date'[Date] <= EndDate_CurrentYear),

        "Total Sales Previous Year", CALCULATE(SUM('Sales'[Amount]), 'Date'[Date] >= StartDate_PreviousYear && 'Date'[Date] <= EndDate_PreviousYear)

    )

explanation:

We define variables for the current year and previous year, as well as start and end dates for both years.

We use ADDCOLUMNS to create a new table with additional calculated columns.

We use CROSSJOIN to create combinations of product categories and customer regions.

We add columns for "Category" and "Region" to identify the product category and customer region.

We calculate "Total Sales Current Year" and "Total Sales Previous Year" for each combination of category and region.

The resulting table provides a detailed analysis of sales performance by product category and customer region, including total sales for the current year and the previous year.

These examples demonstrate the versatility of DAX for complex calculations in business intelligence and data analysis scenarios. They often involve combining various DAX functions to solve specific analytical challenges.