CODEWITHSUNDEEP
daxpowerpivot
Dax
Dax

Reputation: 1

DAX occurrence between 2 dates

I need to count for each occurrence between its open date and closed date.

For example, I have the following data:

ID  beg_dte cmpt_dte
1   8/12/2011   6/29/2012
2   11/3/2011   8/31/2013

I need to know if there is a DAX formula to display the data like:

ID  2011    2012    2013
1    1       1       0
2    1       1       1

I tried the following DAX calculation but it only gives me the last date and not all in between dates.

=CALCULATE(
COUNTROWS(
FILTER(
table,table[ID]
=CALCULATE(
max(table[ID]),all(table),table[ID] = EARLIER(table[ID]),table[CMPT_DTE] <= values(Calendar[DateKey])))),LASTDATE(Calendar[DateKey]))

Upvotes: 0

Views: 232

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

If you create a separate table for the years and then load both tables into the Data Model, you can create a pivot table like this:

Pivot Table

Where you use the following measure in the Values box:

YearBoolean = IF((YEAR(MIN(Table1[beg_dte]))  <= MAX(Years[Year])) &&
                 (YEAR(MAX(Table1[cmpt_dte])) >= MAX(Years[Year])),
                 1, 0)

Upvotes: 0

Related Questions