Forum Discussion
running total
Hello All,
I have one scenario which I am trying to do
I hava table having ticket data.
Ticket No | Data Create | Day| Ticket allotted per day 1 |2017-08-01 | 1 | 26 2 |2017-08-01 | 1 | 26 3 |2017-08-02 | 2 | 26 4 |2017-08-03 | 3 | 26 5 |2017-08-03 | 3 | 26
Now the solution I want to see as follows:
Day | Actual Ticket Per Day | Actual Running Total | Allotted Running Total 1 | 2 | 2 | 26 2 | 1 | 3 | 52 3 | 2 | 5 | 78
Now I can calculate Actual Running Total, but I am facing issues calculating Allotted Running Total.
Any help with calculating Allotted Running Total is appreciated
Let me know if this solves your problem. I find it easier to write the code based on your data:
https://www.dropbox.com/s/b2iv4gtmoofx5kb/Running%20total.pbix?dl=0
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
7 Replies
Is the value of Ticket allotted per day the same for all the rows in the same day? If so, you can do as follows:
Allotted Running Total := VAR CurrentDay = MAX ( Table[Day] ) CALCULATE ( SUMX ( SUMMARIZE ( Table, Table[Day], Table[Ticket allotted per day] ), Table[Ticket allotted per day] ), Table[Day] <= CurrentDay )If, on the other hand, the value might be different for the same day, then this code will result in a wrong result (but, at this point, you have a modeling issue).
That said, it would be better to store the "ticket alloted per day" in a table containing one row per day, in that case the solution would be a much better one.
I have a table with ticket allocated per day month wise.
This number comes from a monthly allocated ticket / number of days in month. So I created a monthwise table having ticket allocated per day
I am not getting the desired result.
What am I getting is same value for throught out the days. i.e. 26
Well, prepare a PBIX file with some demo data, and I will send you the right code. Writing blind is always challenging, I probably missed something.
Have fun with DAX!
Alberto Ferrari
http://www.sqlbi.com
