Forum Discussion
running total
- 9 years ago
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
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 am not getting the desired result.
What am I getting is same value for throught out the days. i.e. 26
- AlbertoFerrari9 years ago
Most Valuable Professional
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- ashaikh9 years ago
Helper III
Hello Alberto,
Let me put my query this way. And i would appreciate if you can help me.
Lets not consider me having an anyother table for Ticket Allotment. I am just having one ticket table.
Now Ticket allotment is calculated in following way.
Total Number of Ticket Allocated/Number of days in a month.
For Example: September, 800/30 = 26.67 ~ 27
So without having Ticket Allotment table is it possible to calculate Running Ticket Allotment.
Thanks in Advance
- AlbertoFerrari9 years ago
Most Valuable Professional
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
- ImkeF9 years ago
Community Champion
Make sure to drag the date-field from your calendar-table into your report.
If your measures reference 2 different tables, you need a table with a date that is connected to both of them. Only date-fields from this table will lead to correct results in your report!