Forum Discussion

Ephraim404's avatar
Ephraim404
Regular Visitor
2 years ago

Cumulative total on a weekly basis with text based values

Hello

 

I'm trying to calculate the cumulative value on a weekly basis for deliveries in logistics.
So starting from two time stamps, I calculated the relative difference between the two.
Based on the value of the time difference I then gave a "text name" by using a conditional column with in total 4 values (Name: Delivery Precision Visual - subcategories: Missing, Pre Time, On Time, Late).
I also have a separate column tracking the arrival week (Name: Arrival week).
Those colums I have made in the power query.


Now to the point: To calculate the cumulative total I used following formula via Quick Measures:

Count of Delivery Precision Visual running total in Arrival Week =
CALCULATE(
    COUNTA('Sheet1'[Delivery Precision Visual]),
    FILTER(
        ALLSELECTED('Sheet1'[Arrival Week]),
        ISONORAFTER('Sheet1'[Arrival Week], MAX('Sheet1'[Arrival Week]), DESC)
    )
)
 
This measure I used in the visualization:

 

Now as you can see, in the weeks where I don't have all 4 values in a specific week (most visable in week 29 & 52), the total value of the previous weeks isn't shown. The code is only taken into account the values present in that week + the weeks before of those values. Yet I want a continuation of the measures who are not present in that specific week.
How do I fix this?

Thanks a lot in advance!

1 Reply