Forum Discussion
Measure that replicates sumifs from Excel with a connected date table - .pbix example attached
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
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
3 Replies
- mahoneypat
Microsoft Employee
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
- Ashish_Mathur
Super User
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.
- 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])
