Hello,
I'm new to Power Pivot and would appreciate any tips trying to work out a Calculated column or Measure that is a conditional count of days like the example in the picture
I need a count of days...
... if the Name matches a Name already in the column and...
... since the last "Event type A" for that matched Name
I'm thinking the calculation needs to be a MAX value, filtered? Something like: 'Find the max date date by filtering the Event and Name , where the name 'equals the Name on the current row.'
So to start with I'm trying to work out what the effective equivalent in DAX is of the "@" in an Excel table. Is it right that I can use the EARLIER function?
I'm not getting very far with this:
=calculate(MAX(tbl_Events[Event date]),FILTER(ALL(tbl_Events), tbl_Events[Name] = EARLIER(tbl_Events[Name]))
Any tips greatly appreciated.
(Cross posted here)
With the measure you mentioned, you should receive for Name 2 the date of 3 Jun 2015, the latest one for that Name.
You need a second criteria, to pass to MAX only dates smaller than the current event date, something like:
tbl_Events[Name] = EARLIER(tbl_Events[Name])&&tbl_Events[Date] < EARLIER(tbl_Events[Date])
Many thanks for your reply. Despite devoting 2 days to trying to learn basic Power Pivot, I'm severely struggling with the steep learning curve, so have returned to working a solution in Excel using COUNTIFS and MIN & SMALL. I'll try to return to this approach when I have more than a week spare...
