Measure that replicates sumifs from Excel with a connected date table - .pbix example attached

[Anonymous]

I am trying to replicate a sumifs from excel into a measure

I have two tables: Data Table that is connected to a Date Table

I want my sumifs measure to sum the values based on two criteria:

1. Condition of the field 'Start or End date" must be = "Start Date", and

2. The date is < or = to the date from my date table.

I've gotten this to work if the date table is NOT attached. But I need the date table attached to the data table for other visuals I need.

I've attached an example .pbix and below is a picture of what I'm trying to replicate in Excel

https://1drv.ms/u/s!AvCirXUmRp-JhcEC8q0HiqMlMjLQUQ?e=tDURhu

 

[amitchandak]

@Anonymous , Try with date from the date table

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
sumx(summarize(calculateTable('Data', filter(all('Date'), 'Date'[Date] <_max)), [Value]),[Value])

[Ashish_Mathur]

Hi,

You may download my PBI file from here.  Since there are way too many dates in your Date Table, you will see all those dates in the visual as well.

Hope this helps.

[mahoneypat · Accepted solution]

Please try this measure expression to get your result.

Sum Start Dates =
VAR maxdate =
    MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Unpivoted start and end date'[Value] ),
        ALL ( 'Date Table'[Date] ),
        'Unpivoted start and end date'[Date] <= maxdate,
        'Unpivoted start and end date'[Start or End Date] = "Start Date"
    )

Regards,

Pat