Running Total/Cumulative Total in DAX

 Say we have below data:

Now if we want to calculate the running total, something like below

Through DAX, we can write below measure:

Cumulative Amt =

CALCULATE

(

    SUM(table_orders[Bill_amt]),

    FILTER

    (all(table_orders),

    table_orders[OrderDate] <= MAX(table_orders[OrderDate]

    )

    )

    )

But there is a catch here.

Say we have HotelName as slicer then cumulative total would not be just for that hotel.

We can modify the DAX measure as below:

Cumulative Amt slicer filter =

CALCULATE

(

    SUM(table_orders[Bill_amt]),

     FILTER(ALLSELECTED(table_orders), table_orders[OrderDate] <= MAX(table_orders[OrderDate]))

)

Ideally if some HotelName filter is applied then we need cumulative total for just the hotel name.

SQL query:

select OrderID ,
OrderDate,	
HotelName,
FoodItem,
Bill_amt,
sum(Bill_amt) over (order by OrderDate) 
from Orders

selectedvalue || HasOneValue || Values || CONCATENATEX

 SELECTEDVALUE : Displays the value that is selected. If multiple values are selected it displays blank

selectedvalue2 = SELECTEDVALUE(dim_date[Year])

HASONEVALUE = It returns true/false. Can be used inside IF function.

Say, we want to display the selected year and if multiple values are selected display them as concatenated values.

SelectedYear = IF

(HASONEVALUE(dim_date[Year]), SELECTEDVALUE(dim_date[Year]), CONCATENATEX(VALUES(dim_date[Year]), dim_date[Year], ","))

CONCATENATEX : displays all values inside the parameters  with delimiters that was specified.

If we observe we have used VALUES function as well.

VALUES : It returns single column with unique values.

If we don't use VALUES then it returns values like 2024, 2025, 2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025,2024, 2025.

Basically for every row in dim_date it concatenates values. To avoide that we use VALUES function since it returns unqiue values.

union

ALLNOBLANKROWS

RelatedTable function

FILTER function - count of orders whose bill amount is greater than avg bill amount

ALLEXCEPT

Calculating %age in Power BI - ALL function

 Say we have below table :

Now if we want to calculate the %age of expenses, we need the total expense.

Then say we want to calculate %age total expense for Transportation, then we can use below formula:

=5000/47500 = 0.105 = 10.5%

Now to calculate the total expense in DAX, we need to use "ALL" function

Total Expenditure(ALL) = CALCULATE([TotalExpenditure] , ALL(Sheet1[Category]))

Then %age can be calculated as below:

% Total Expenditure = DIVIDE([TotalExpenditure], [Total Expenditure(ALL)], 0)

Difference Between DAx functions : Date & DateValue

 DAX function Date requires 3 parameters as number

Date(year, month, day)

Example: Date(2022, 2, 17)

This will give date as "2022-02-17"

DateValue converts a text string to date time

Example: we have year in one column and month name as "feb" in another column.

And we want to combine year & month to date column, we can write as below

 DATEVALUE("1-"&fact_sales_Iphone14[month]&"-"&fact_sales_Iphone14[Year])

Create Date table in Power BI

 Date table can be created in power bi in multiple ways:

Using the CALENDAR DAX function:

In Power BI, click New table.

Name the query, example it can be dim_date or just date or date_dim

dim_date = CALENDAR(DATE(2017, 01, 01), DATE(2027-12-31))

This will create dim_table with column "date" having dates ranging from 1-jan-2017 to 31-dec-2027

Tricky part to remember: Parameters inside CALENDAR has to be date. We just can't hard-code '2017-01-01' and '20127-12-31'

Thats the reason we use DATE function 

CALENDARAUTO DAX function:

CALENDARAUTO() is a function that scans all datetime columns in the model. Picks the min & max dates from those columns and returns contiguos dates as table with "Date" column.

Optionally we can pass number 1 to 12 to the function which is considered as the end month of fiscal year

Creating Calendar Date using M Langauage/PowerQuery

Open Power Query, take the blank query and type below M code:

{Number.From(#date(2017,9,1))..Number.From(#date(2022,12,31))}

Convert the list to table and change the data type to date. 

Time Intelligence Functions

 TOTALYTD

TOTALQTD

TOTALMTD

DATESYTD

DATESMTD

DATESQTD

DATEADD

SAMEPERIODLASTYEAR

PARALLELPERIOD

DAX Functions

 USERELATIONSHIP

generates table

-----------------------------------------------

DATATABLE

UNION

SUMMARIZE

DISTINCT

FILTER 

GENERATESERIES

CALENDAR

CALENDARAUTO

--------------------------

Aggregate Functions:

SUM

MIN

MAX

AVERAGE

COUNTROWS

DISTINCTCOUNT

-----------------------------------

Information Functions:

ISBLANK

ISFILTERED

ISCROSSFILTERED

----------------------------------------

Logical Functions

IF

AND

SWITCH

------------------------------------------

Date & Time Functions

DATEDIFF

DATEVALUE

-------------------------------------------

FILTER Functions

CALCULATE

FILTER

VALUE

ALL

TOPN