Forum Discussion
Calculating running weekly total
Hello,
I need your help! I am attempting to create a Power BI run chart which will illustrate weekly accumulate unit totals. Unfortunately, the source data only documents when the units are collected for that particular week but we need to show "0" for the week on the chart were there are no data units collected.
We use a sperate calendar table to list weeks & year and the source data is in another table which only notes the week when data was collected - in this case only week 3 accumlated '252' units so we would like "0" place into run chart were there are no units.
Thank you in advance,
Don
| Year | week | Units |
| 2020 | 1 | |
| 2020 | 2 | |
| 2020 | 3 | 252 |
| 2020 | 4 | |
| 2020 | 5 | |
| 2020 | 6 |
Hi,
Please try this measure to replace the original [Units]:
Check = IF(MAX('Table'[Units])=BLANK(),0,SUM('Table'[Units]))And it shows:
Best Regards,
Giotto Zhi
5 Replies
Anonymous
You could use a IF statement to check if the value is blank and assign 0
Something like this..
Measure = IF(ISBLANK(SUM(Table[Units])),0,SUM(Table[Units])) or Measure = VAR Units_Total = SUM(Table[Units]) RETURN IF(ISBLANK(Units_Total),0,Units_Total)If it helps, mark it as a solution
Kudos are nice too
Try link
calculate(sum(table[unit]),filter(all(table]),Table[week]<=Max(table[Week])))
Better to have date table and move this calc there
calculate(sum(table[unit]),filter(all(Date]),Date[week]<=Max(table[Week])))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on LinkedinHi,
Please try this measure to replace the original [Units]:
Check = IF(MAX('Table'[Units])=BLANK(),0,SUM('Table'[Units]))And it shows:
Best Regards,
Giotto Zhi
