Count number of ID's in Target date and actual date in a same table / matrix

[pranit1997]

Data Structure:

BU	ID	Target Date	Filed Date
BU1	12345	1/1/2022	2/10/2022
BU1	56789	1/1/2022
BU1	12567	2/20/2022	2/15/2022
BU1	14568	3/1/2022	2/25/2022

Required Output

BU		Jan-2022	Feb-2022	Mar-2022
BU 1	Target	2	1	1
	Filed		3

Trying to figure this out since a long time but still stuck!

New to power bi. Request anyone to help me out with this.

[amitchandak · Accepted solution]

@pranit1997 , Create a common date table join with both dates one date will be inactive, Use userelationship to activate the join. Use SHOW on row in matrix visual

Assume Target date is active 

Target = count(Table[ID])

Filed date inactive join

Filed =

calculate(count(Table[ID]), userelationship(Table[Filed Date], 'Date'[Date]))

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...