# How to use variables in DAX:
TotalSales_w_increase =
VAR increase = 0.05
Return 'Calculations'[TotalSales] + ('Calculations'[TotalSales] * increase)
# How to use Averagex vs Average:
AvgProfit_x = AVERAGEX(Sales, Sales[LinePrice] - Sales[LineCost])
This does not depend on another calculated column
but Average() depends on another column, Here for example sales[profit] column.
AvgProfit = AVERAGE(Sales[Profit])
# How to use calculate function:
Total sales for bike-----
2018 Bikes Revenue = CALCULATE(
SUM(Sales[LinePrice]),
FILTER(Sales, Sales[ProductCategory] = "Bikes"),
FILTER(Sales, YEAR(Sales[OrderDate]) = 2018)
)
You can do any aggregation with Calculate function and use filter inside.
Another example of the Calculate function is here down below-
Total sales on the last selected date =
CALCULATE (
SUM ( Sales[Sales Amount] ),
'Sales'[OrderDateKey] = MAX ( 'Sales'[OrderDateKey] )
)
# Working with Dates:
DayNo = Day(Dates[Date])
DayShortName = FORMAT(Dates[Date], "ddd")
You can get the full list of functions in this link: Date and time functions (DAX) - DAX | Microsoft Learn
DAX expression for Quater over Quater Change-
TotalSales QoQ% =
IF(
ISFILTERED('Dates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_QUARTER = CALCULATE([TotalSales], DATEADD('Dates'[Date].[Date], -1, QUARTER))
RETURN
DIVIDE([TotalSales] - __PREV_QUARTER, __PREV_QUARTER)
DATEDIFF(<Date1>, <Date2>, <Interval>)
You can find the difference between two dates using datediff
Order2Delivery = DATEDIFF(Sales[OrderDate], Sales[DeliveryDueDate], DAY)
Sales Count YoY% =
IF(
ISFILTERED('Dates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR = CALCULATE([SalesCount], DATEADD('Dates'[Date].[Date], -1, YEAR))
RETURN
DIVIDE([TotalSales] - __PREV_YEAR, __PREV_YEAR)
)
Sales Count YoY% =
IF(
ISFILTERED('Dates'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __PREV_YEAR = CALCULATE([SalesCount], DATEADD('Dates'[Date].[Date], -1, YEAR))
RETURN
DIVIDE([SalesCount] - __PREV_YEAR, __PREV_YEAR)
)
How to create a data table:
Dates = CALENDAR(MIN(Sales[OrderDate]), MAX(Sales[OrderDate]))
