Create sumif measure using date as filter .pbix example attached

[Anonymous]

I'm trying to recreate a sumif in excel and using the date as my filter. The date table seems to make this difficult (for me). I think this should be easy but I'm stuck. Here is an example model: https://1drv.ms/u/s!AvCirXUmRp-JhcBp6JcNo0IvAG9-Xg?e=SekKRi

Below is my data in excel with the result or visual I want to create at the bottom. My excel formula is this: =SUMIF(A1:A6,"<="&A10,B1:B6) where it looks at the date and filters the sum based on the date. How do I create a measure like this to replicate my excel formula? 

[v-deddai1-msft]

Hi @Anonymous ,

For calculated column , you can use the following formula:

Column = CALCULATE(SUM('Data Table'[Value]),FILTER('Data Table','Data Table'[Date]<=EARLIER('Data Table'[Date])))

For measure , you can use the following measure:

accumulative total = CALCULATE(SUM('Data Table'[Value]),FILTER(ALL('Data Table'),'Data Table'[Date]<=MAX('Data Table'[Date])))

If this post help, please consider accept it as the solution to help other member find it more quickly.

Best Regards,

Dedmon Dai

[jdbuchanan71]

@jcueland

It's going to be something on this line.

Cumulative Value = 
VAR _MaxDate = LASTDATE ( 'Date Table'[Date] )

RETURN
CALCULATE (
    SUM('Data Table'[Value]),
    FILTER ( 
        ALLSELECTED('Date Table'[Date]),
        'Date Table'[Date] <= _MaxDate)
)