Forum Discussion

bisted's avatar
bisted
Frequent Visitor
2 years ago
Solved

How to make a cumulative total for a second date column

I have a data set that has two date columns, a delivery date and a sale date. As delivery date is the column we refer to most often it is the one with an active relationship to my date table, and I have been able to create a cumulative measure for this easily enough 

Cumulative Delivery Items = CALCULATE([Filtered Items],FILTER(ALL(Sales),Sales[Delivery Date]<= MAX(Sales[Delivery Date])))

(where Filter Items is a measure that is the sum of all items in the table).
 
However, I have been unable to get the same result for the Sales Date column. I have set up an inactive relationship between it and the date column, and tried:

Cumulative Sales Items = CALCULATE([Filtered Items],FILTER(ALL(Sales),Sales[Date Confirmed]<= MAX(Sales[Date Confirmed])),USERELATIONSHIP('Date Table'[Date],Sales[Date Confirmed]))

 and variations on a similar theme, but have had no luck yet, does anyone have any advice?
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    bisted , Try using below measure 

     

    Cumulative Sales Items =
    CALCULATE(
    [Filtered Items],
    FILTER(
    ALL(Sales),
    Sales[Date Confirmed] <= MAX(Sales[Date Confirmed])
    ),
    USERELATIONSHIP('Date Table'[Date], Sales[Date Confirmed])
    )

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    bisted , Try using below measure 

     

    Cumulative Sales Items =
    CALCULATE(
    [Filtered Items],
    FILTER(
    ALL(Sales),
    Sales[Date Confirmed] <= MAX(Sales[Date Confirmed])
    ),
    USERELATIONSHIP('Date Table'[Date], Sales[Date Confirmed])
    )

    • bisted's avatar
      bisted
      Frequent Visitor
      2 years ago

      Hi bhanu_gautam, thank you for looking into this, unfortunately that just re-writes the above measure that I had tried, which doesn't work (I have tried it with your new formatting though in case it as an odd error related to that, but still no luck).

      • bisted's avatar
        bisted
        Frequent Visitor
        2 years ago

        Just to add, bhanu_gautam's approach works perfectly, it was my mistake of making a relationship between a date column in my date table that was whole dates with a date column that I hadn't noticed also contained times, adding a calculated column with rounded dates to  use the relationship on worked fine.

         

        Thank you bhanu_gautam!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solution bhanu_gautam  provided, and i want to offer some more informtion for user to refer to.

    hello bisted , you can try the following solutions.

    Sales Items =
CALCULATE (
    [Filtered Items],
    USERELATIONSHIP ( 'Date Table'[Date], Sales[Date Confirmed] )
)

    Then create a new measure

    Cumulative Sales Items =
IF (
    [Sales Items] <> BLANK (),
    SUMX (
        FILTER ( ALLSELECTED ( 'Date Table' ), [Date] <= MAX ( 'Date Table'[Date] ) ),
        [Sales Items]
    )
)

    Best Regards!

    Yolo Zhu

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