Measure to Calculate Percentage of Days Matching Conditions

DAX DAX Calculations
1

Good afternoon! I’m in a little over my head with this one, and I’d appreciate any input from the community…

I’ve attached a PBIX with some sample data. The data includes a Dates table and a Data table. The Data table has two columns: EventDate and EventType (EventType values are a number from 1 to 5). I need a measure to calculate the percentage of days within the selected date range where the following conditions are met:

  • No events occurred, OR
  • Only events of Type 1 occurred

Any ideas how to resolve this? I’m assuming that some sort of virtual table would be involved, but that’s something I’ve not done much of before, and I’m not really sure how to tackle it. All the examples I’ve seen in the videos I’ve watched seem more simplistic and I’ve had trouble applying them to this scenario.

Any help would be greatly appreciated!

eDNA Sample.pbix (142.4 KB)

2

_DayCount =

CALCULATE(
COUNTROWS(Dates),
KEEPFILTERS(Dates[Year])
)

% of Days with Events =
DIVIDE(
[_Measure], [_DayCount], 0)

3

image
image692×427 16.7 KB

4

@DaveC Try this…

image
image1114×623 65 KB

_Measure =

VAR _MinDate = MIN(Dates[Date])
VAR _MaxDate = MAX(Dates[Date])
VAR _Rows =
CALCULATE(
COUNTROWS(Data),
FILTER(
Data,
Data[EventDate] >= _MinDate &&
Data[EventDate] <= _MaxDate)
)

RETURN
_Rows

5

Thanks for the suggestions, @mbraun! I’ll give it a shot later today and share my resutls.

6

Hi @DaveC - Please confirm if issue is resolved and we can mark this post as Solved.

Thanks
Ankit J