Previous rows value based on date (Nested Window Function from Azure DatbricksSQL in Power BI logic)

[ssspk]

Hi,

How to achieve the values from previous row in Power BI (which means nested window functionality in Azure databricks).

I can exectre the query and display the results in Azure databricks. I would like to create a line chart in powerbi cloud using

Y axis as "Running_Total" and X axis as "Date_Value" values.

Query:(Azure databricks query)

SELECT to_date(servertime, 'yyyy-MM-dd') AS Date_Value,
count(*) AS Event_Count,
sum(count(*)) OVER (ORDER BY to_date(servertime, 'yyyy-MM-dd') ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Running_Total
FROM traces_p.data
INNER JOIN traces_p.type ON data.type = type.id
WHERE typename IN ('bookmark', 'app')
GROUP BY to_date(servertime, 'yyyy-MM-dd')
ORDER BY 1;

Result:

Date_Value     Events_Count      Running_Total
2023-07-17       1209                  1209
2023-07-18       1454                  2663
2023-07-19       1416                  4079
2023-07-20       1284                  5363
2023-07-21       1420                  6783
2023-07-22         48                    6831
2023-07-23        103                   6934

2023-07-24        2093                 9027
2023-07-25       1755                 10782
2023-07-26       1555                 12337

power bi data

How to use this query in power bi? Any DAX query ? or Formula can be used?

Thank in Advance.

[ssspk · Accepted solution]

Created a mew measure for the colum and used the Fn like below. Its working,

RunningTotal = CALCULATE (

    SUM ( [EventsCount] ),    

        ALL ( data_customized ),

        data_customized[servertime] <= EARLIER (data_customized[servertime]))

Thank you.

[ssspk]

Hi,

Below code works for my problem and displays the Running total properly.

As solution,

RunningTotal = CALCULATE (

    SUM ( [EventsCount] ),    

        ALL ( data_customized ),

        data_customized[servertime] <= EARLIER (data_customized[servertime]))

Thanks

[tharunkumarRTK]

@ssspk 

There are many ways to achieve running total. Assuming you aready have a measure called eventCount in your model.

Calculate([EventCount], Filter(Allselected(TableName) , Table[Date_value] <=Max(Table[Date_Value]) ))

for your reference: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[ssspk]

Hi Tharun,

Thank you for reply.But i am getting the below error.

Attached image for your reference.

Thank you.

[tharunkumarRTK]

Hi 
You are creating a caculated column, I gave an example syntax for measure. 
Also, while writing the measure I assumed that you have a measure called EventsCount 
if that is not the case then please create the measure first.

[ssspk]

Hi Tharun,

I am getting the below error in the Table view itself.Attached screen shorts.

Its says cannot found or may be not used in the expression is thrown.

Thanks in advance.

[ssspk]

HI,

Tharun Thank you for your reply and time. I am a new bee to the topic.

yes its a calculated column.

For new colum i have used like below,

= Table.AddColumn(#"Renamed Columns", "RunningTotal", each CALCULATE([EventsCount], Filter(Allselected(data_customized) , Table[servertime] <=Max(Table[servertime]) )))

Atached images for your reference,

Thank you.