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 h...
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])
    )

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!