running total

[ashaikh]

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

[AlbertoFerrari]

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.

[ashaikh]

I am not getting the desired result.

What  am I getting is same value for throught out the days. i.e. 26

[ImkeF]

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!

[AlbertoFerrari]

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

[ashaikh]

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

[AlbertoFerrari · Accepted solution]

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

[ashaikh]

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