Forum Discussion

lopazopy's avatar
lopazopy
Frequent Visitor
3 years ago
Solved

Running total with dates within a separate date category

I'm trying to find a running total for orders packed within a certain PromiseDate "bucket". The dates may be in any order in my table, but the way I'm thinking they are ordered by PromiseDate in desc...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I assume the expected outcome is a measure, not a calculated column.

    Please check the below picture and the attached pbix file.

     

     

    Expected outcome running total: =
IF (
    HASONEVALUE ( Data[PromiseDate] ),
    CALCULATE (
        SUM ( Data[PackLbs] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE (
                ALL ( Data ),
                Data[PromiseDate],
                Data[OrderNum],
                Data[PackDate],
                Data[PackLbs]
            ),
            ORDERBY ( Data[PackDate], ASC, Data[OrderNum], ASC ),
            ,
            PARTITIONBY ( Data[PromiseDate] )
        )
    )
)

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I assume the expected outcome is a measure, not a calculated column.

Please check the below picture and the attached pbix file.

 

 

Expected outcome running total: =
IF (
    HASONEVALUE ( Data[PromiseDate] ),
    CALCULATE (
        SUM ( Data[PackLbs] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            SUMMARIZE (
                ALL ( Data ),
                Data[PromiseDate],
                Data[OrderNum],
                Data[PackDate],
                Data[PackLbs]
            ),
            ORDERBY ( Data[PackDate], ASC, Data[OrderNum], ASC ),
            ,
            PARTITIONBY ( Data[PromiseDate] )
        )
    )
)

 

lopazopy's avatar
lopazopy
Frequent Visitor
3 years ago

Thanks so much! I had not seen the WINDOW function being used yet. I had to upgrade to the latest Desktop version of PowerBI for it to work.