Distinct Count Total issue based on Type and latest period

[dexter2424]

Hi All, 

I'm struggling with an issue, maybe it's easy to solve, or not, but I'm get lost, and the distinctcount doesn't work properly for me.

I have the following table

Policy Number	Reporting Period	Type
A1	2021-01-01	Large
A1	2021-02-01	Not Large
A1	2021-03-01	Large
A1	2021-05-01	Large
B2	2021-01-01	Not Large
B2	2021-03-01	Not Large
B2	2021-05-01	Large

I would like to distinct count the number of "Large" statuses with the following criteria:
I have a slicer, what shows the Max Reporting period date.
Once I select for example 2021-05-01 (YYYY-MM-DD), I would like to see the distinctcount result as 2, because on 2021-05-01 I have 2 Large values.
Once I select 2021-04-01, I would like to see 1, because A1 was Large on 2021-03-01 and B2 was Not Large on 2021-03-01. 
Once I select 2021-02-01, I would like to see 0, because A1 on the selected period is "Not Large", and B2 is also a "Not Large"

In summary, if there is an exact match with the dates, then count as 1 if the Type is "Large", and doesn't count if the Type is "Not Large", 
If there is no record in the selected period (for example A1 on 2021-04-01) then check the second highest date, what is 2021-03-01 and if it's "Large" count as 1, if it's "Not Large" count as 0.

Thank you very much in Advance!

Large = 
var _max = CALCULATE(MAXX(Table,Table[Reporting Period]),ALLEXCEPT(Table,Table[Policy Number],Table[Type]))

return

CALCULATE(DISTINCTCOUNT(Table[Policy Number]),FILTER(ALLEXCEPT(Table,Table[Policy Number]),Table[Type] = "Large" && Table[Reporting Period] = _max))

[DataInsights · Accepted solution]

@dexter2424,

Try this solution.

1. Create date table with a relationship to the data table:

2. Create measure:

Large = 
VAR vSlicerEndDate =
    LASTDATE ( ALLSELECTED ( Dates[Date] ) )
VAR vResult =
    SUMX (
        VALUES ( FactTable[Policy Number] ),
        VAR vMaxDate =
            CALCULATE (
                MAX ( FactTable[Reporting Period] ),
                FactTable[Reporting Period] <= vSlicerEndDate
            )
        VAR vType =
            CALCULATE ( MAX ( FactTable[Type] ), FactTable[Reporting Period] = vMaxDate )
        RETURN
            IF ( vType = "Large", 1, 0 )
    )
RETURN
    vResult

3. Create date slicer using the date table (Dates[Date]).

4. Create visual:

---------------------------------------------

---------------------------------------------

[Mi_80]

Hi,

I am using this solution and it works great thanks.  I have a quick question.  If I wanted to display this measure in a bar chart instead with the value for each month acros x axis how could that be achieved?  Basically I would like to not have the slider but do the calculation for using the value of the first date of each month instead.  Thanks

[dexter2424]

Thanks you DataInsights! That's what I'm looking for.

In the meantime I have figured out another solution what is working, I just leave it here, maybe somebody findes it useful:

Creating a max date measure:

Max_Date = CALCULATE(MAXX(Table,Table[Reporting Period]),ALLEXCEPT(Table,Table[Policy Number]))

and the main measure:

Large = COUNTX(FILTER(Table,Table[Type] = "Large" && Table[Reporting Period] = [Max_Date]),Table[Policy Number])

[amitchandak]

@dexter2424 , Try a measure like

measure =
Var _max = maxx(allselected(Table), Table[Date])
var _max1 = maxx(filter(Table, Table[date] <_max), Table[Date])
var _cnt = countx(filter(Table, Table[Date] =_max && Table[Type] ="Large"),[Policy Number])+0
var _cnt1 = countx(filter(all(Table), Table[Date] =_max1 && Table[Type] ="Large"),[Policy Number])
return
if(_cnt =0, _cnt1, _cnt)

[dexter2424]

Thanks Amitchandak, but it still picks up the latest date.

I have 4 different policies, and the highest date is 01-04-2021 there, all of the policies are unique, and for 3 of them the latest date is 01-04-2021 and one of them is 01-03-2021. The result of your query above gives me 3, because just counts the 3 highest ones, where the max date is 01-04-2021 and ignore the 01-03-2021 in another policy