DAX Common Scenarios

YTD & MTD Totals, Conditional Calculations, Rolling Averages, Hierarchical Relationships within Microsoft Power BI.

A. Calculating year-to-date (YTD) and month-to-date (MTD) totals:

• YTD Sales: CALCULATE(SUM(Sales[Amount]), DATESYTD(Calendar[Date])) 

Calculates the sum of the "Amount" column in the "Sales" table for the year-to-date period based on the "Date" column in the "Calendar" table.

• MTD Revenue: CALCULATE(SUM(Revenue[Amount]), DATESMTD(Calendar[Date])) 

Calculates the sum of the "Amount" column in the "Revenue" table for the month-to-date period based on the "Date" column in the "Calendar" table.

B. Applying conditional calculations using IF and SWITCH functions:

• Conditional Sales: IF(Sales[Amount] > 1000, "High", "Low") 

Checks if the value in the "Amount" column of the "Sales" table is greater than 1000. If true, it returns "High"; otherwise, it returns "Low".

• Category Group: SWITCH(Sales[Category], "Electronics", "Technology", "Clothing", "Apparel", "Other") 

Evaluates the value in the "Category" column of the "Sales" table. If the value matches "Electronics", it returns "Technology". If it matches "Clothing", it returns "Apparel". Otherwise, it returns "Other".

C. Implementing rolling averages and moving totals:

• 3-Month Rolling Average: CALCULATE(AVERAGE(Sales[Amount]), DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -3, MONTH)) 

Calculates the average of the "Amount" column in the "Sales" table over a rolling period of 3 months, based on the "Date" column in the "Calendar" table.

• 7-Day Moving Total: CALCULATE(SUM(Sales[Amount]), DATESINPERIOD(Calendar[Date], LASTDATE(Calendar[Date]), -6, DAY)) 

Calculates the sum of the "Amount" column in the "Sales" table over a moving period of 7 days, based on the "Date" column in the "Calendar" table.

D. Handling hierarchical and parent-child relationships in DAX:

• Parent Total: SUMX(RELATEDTABLE(Departments), Departments[Sales]) 

Sums up the "Sales" column in the "Departments" table for all related rows.

• Descendants Count: COUNTROWS(Descendants(Products, Products[Category], SELF_AND_BEFORE)) 

Counts the number of rows in the "Products" table that are descendants of a specific category in the "Category" column, including the category itself.

E. Creating advanced calculated tables and columns:

• Calculated Table: NewTable = SUMMARIZE(Sales, Sales[Category], "Total Amount", SUM(Sales[Amount])) 

Creates a new table called "NewTable" that summarizes the "Sales" table by the "Category" column and calculates the total sum of the "Amount" column for each category.

• Calculated Column: Discounted Price = Sales[Price] * (1 - Sales[Discount]) 

Calculates the "Discounted Price" by multiplying the values in the "Price" column and (1 - Discount) in the "Sales" table. The result is stored in a new column called "Discounted Price".