How to apply order by Window function in DAX

May 27, 2023

In PowerBI, I want to create a calculated table from table t. Using DAX, how can I write the equivalent of the following SQL query. Basically, here I am finding running sum (cumsum) using cnt ordered by date.

select
    date,
    cnt,
    sum(cnt) over (order by date) as cumsum
from
    t

This is an example output. The source table has columns d and cnt. This query adds the table cumsum.

date, cnt, cumsum
2022-01-01, 13, 13,
2022-01-02, 14, 27
2022-01-03, 18, 45
...

>Solution :

Here you go.

Table1 :

Table2:

Table2 = 
ADDCOLUMNS(
    SUMMARIZE(Table1, Table1[date], Table1[cnt]),
    "cumsum", 
    VAR x= Table1[date]
    RETURN
    CALCULATE(SUM(Table1[cnt]), REMOVEFILTERS(), Table1[date] <= x )
)