Forum Discussion
Adding calculated values to table pre-filled with values
Hi, I want to create the below table where in i have some prefilled dtaa present. On this data I then want to add daily calculated values from other table using formulas please assist me how i can do so:
| Date | Start of Day | Incoming | Handled | End of Day |
| 30-Mar | 17 | 2 | 5 | 14 |
| 31-Mar | 14 | 2 | 3 | 13 |
| 1-Apr | 12 | 12 | 4 | 20 |
| 2-Apr | 20 | 2 | 5 | 17 |
| 3-Apr | 17 | 2 | -3 | 22 |
| 4-Apr | 22 | 0 | 0 | 22 |
| 5-Apr | 22 | 0 | 0 | 22 |
| 6-Apr | 22 | 2 | 2 | 22 |
| 7-Apr | 22 | 2 | 5 | 19 |
| 8-Apr | 19 | 3 | 3 | 19 |
| 9-Apr | End of day value from previous day | Daily Incoming count | Start of Day + Incoming - End of Day (9th April) | End of day count from last data extract for the day (9th April) |
| 10-Apr | End of Day from 9th April | Daily Incoming | Start of Day + incoming - End of Day (10th April) | End of Day (10th April) |
Here starting from 9-Apr, i want to add data as mentioned:
Start of Day = End of Day from previous day
Daily Incoming = Count of ID from other table for the particular day i.e 9th Apr
End of Day = End of day count of same day 9th April
Handled = (Start of day + Incoming) - End of day
Please guide how i can acheive this. I can import this table into my report and then want to add rows daily using the raw data extract i will pull every morning
3 Replies
You need to take this differently for measure
You have to take
Initial End of Day +Cummlative Incoming - cumulative handled
Cumm Incoming= CALCULATE(SUM(Table[Incoming]),filter(date,date[date] <=max(Table[Date])))
Cumm Handled= CALCULATE(SUM(Table[Handled]),filter(date,date[date] <=max(Table[Date])))
Initial End of Day =
var _min = minx(table,Table[Date])
return
calculate(sum(Table[End of Day]) , filter(Date,Date[Date]=_min))If initial end of the day is 0. you can only work with rest of the two
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Thanks Greg for the valuable input. 🙂 May be I did not state my problem clearly though:
I do not want to add columns to my existing Raw Data extract. I create the Matrix table of using data points from the Raw data sheet and now instead of manually entering data everyday i want to calculate the data in each row daily from my raw data extract taken the next day for ex: tomorrow i.e. 10-Apr when i upload my Raw data, the Table with row for 9th April will auto update for Incoming, End of Day & Handled data
Please help how i can define/caculate values and update the table daily for previous day data
Thanks a ton
Kind Regards,Tushar
