Forum Discussion

Mewan117's avatar
Mewan117
Frequent Visitor
3 years ago
Solved

Rolling Total of Distinct Count String

Hello All,

 

I have a data set with Orders (String) related data that I need to visualize with a rolling sum. These Orders ID's repeat based on the number of items in the order (captured barcode scan index). I need to take the unique ID's and sum them up and show the rolling total month-to-month. FYI the same Order ID would have the same date and time repeating (as shown below).

Example data


I was thinking to count based on Captured barcode scan index = 0, but some orders would get processed multiple times (if the order fails initially). Then it would start at scan index 0 again with the same order ID.  However, the date and time would be different. But I dont know how to write that code.

The current visualization I have below is showing month-to-month values. I need it to keep going up with a rolling total and end with the current total shown

Current Graph

 

Expected Graph (Example)

 

Thank you in advance for your help.

  • Hi, Mewan117 

     

    You can try the following methods.

    Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])))

    Hope this method helps you.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support

    Hi, Mewan117 

     

    You can try the following methods.

    Measure = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])))

    Hope this method helps you.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.