topic Re: Measure to filter table based on 2 date fields (purchase, disposal) in DAX Commands and Tips

Measure to filter table based on 2 date fields (purchase, disposal)

REWINDER — Thu, 07 Jul 2022 05:29:30 GMT

Hi everyone,

Though I thought my problem was solved earlier this week: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-to-filter-rows-based-on-two-dates-begin-and-end-date/td-p/2618360#M76186

It unfortunately was not. (cannot seem to UNsolve that topic)

I am looking to get a measure with Active vehicles number, ClassStructure, By year (>= [PurchaseDate], <=[ReplacementDate], .

Presented but NOT correctly working option was:

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

#Vehicles Active =CALCULATE (
COUNTROWS ( FleetActivity ),
FILTER (ALL ( FleetActivity ),
FleetActivity[PurchaseDate] <= MAX ( dimDate2[Date_pk] )
&& FleetActivity[ReplacementDate] >= MIN ( dimDate2[Date_pk] )
))
-------------------------------------------------------------------------------------

>> My thinking is that the problem lies with the relationship to the Date table ( 1. with [PurchaseDate] and 2. with [ReplacementDate]), So I tried Userelationship: but it gives me the error that it can only be used in a Calculate syntax.

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

active vehicles userelationship = CALCULATE ( COUNTROWS ( FleetActivity ), FILTER ( ALL ( FleetActivity ), FleetActivity[PurchaseDate] <= MAX ( dimDate2[Date_pk] ) && Userelationship(FleetActivity[ReplacementDate],dimDate2[Date_pk]) >= MIN ( dimDate2[Date_pk] ) ))
------------------------------------------------------------------------------------

Date table and relationships:

I wanted to share a Pbix test file, but unfortunately it is not supported.
Also, Sorry I had to re-open this question: if anyone knows how to (UN)solve the other topic, happy to do that.

Thank you in advance!

Re: Measure to filter table based on 2 date fields (purchase, disposal)

amitchandak — Thu, 07 Jul 2022 07:11:12 GMT

REWINDER , change as per need

active vehicles userelationship = CALCULATE ( CALCULATE ( COUNTROWS ( FleetActivity )Userelationship(FleetActivity[ReplacementDate],dimDate2[Date_pk]) )
, FILTER ( ALL ( FleetActivity ), FleetActivity[ReplacementDate] <= MAX ( dimDate2[Date_pk] ) && FleetActivity[ReplacementDate] >= MIN ( dimDate2[Date_pk] ) ))

You need the filter you have used only when you want filter purchase date not the replacement date

this should do

CALCULATE ( COUNTROWS ( FleetActivity )Userelationship(FleetActivity[ReplacementDate],dimDate2[Date_pk]) )

example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

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

Re: Measure to filter table based on 2 date fields (purchase, disposal)

REWINDER — Thu, 07 Jul 2022 08:43:49 GMT

amitchandak , Thank you.

Though it gets the right values at the end of the date range,

at the beginning, it only starts counting values from the Replacement date, see figure 1. Figure 2 at end of Date range. (measures above table for reference).

So the measure on the right gets the numbers right, but is unable to split it by ClassStructure.

Re: Measure to filter table based on 2 date fields (purchase, disposal)

tamerj1 — Thu, 07 Jul 2022 09:10:05 GMT

Hi REWINDER
I assume the active relationship is with the FleetActivity[PurchaseDate] coulmn, then please try

#Vehicles Active = CALCULATE ( COUNTROWS ( FleetActivity ), FleetActivity[PurchaseDate] <= MAX ( dimDate2[Date_pk] ), FleetActivity[ReplacementDate] >= MIN ( dimDate2[Date_pk] ), CROSSFILTER ( FleetActivity[PurchaseDate], dimDate2[Date_pk], NONE ) )

Re: Measure to filter table based on 2 date fields (purchase, disposal)

REWINDER — Thu, 07 Jul 2022 22:03:42 GMT

tamerj1 ,
Thank you for the suggestion, but this solution too only counts total vehicles, and does not separate per Type.
Attempting to make a hybrid out of the three measures now.

Re: Measure to filter table based on 2 date fields (purchase, disposal)

tamerj1 — Fri, 08 Jul 2022 07:56:44 GMT

Hi REWINDER
Would you please provide sample data and on the same screenshot above manually type the correct expected results?

Re: Measure to filter table based on 2 date fields (purchase, disposal)

REWINDER — Mon, 11 Jul 2022 06:32:54 GMT

SOLUTION:

1. remove connections to date table, then:

#ClassActiveCount =

-- Calculate active totals

VAR currentDate =

MAX ( dimDate2[Date_pk] ) -- Saves the last visible date

Var MinCurrentDate =

MIN( dimDate2[Date_pk]) -- Saves first visible date

RETURN

CALCULATE (

COUNTROWS (FleetActivity), -- Computes Active count

FILTER (

FleetActivity,

( FleetActivity[PurchaseDate] <= currentDate -- Where Start date is before the last visible date

&& (FleetActivity[ReplacementDate] > MinCurrentDate -- Where End date is after the last visible date

|| FleetActivity[ReplacementDate] = BLANK()) -- -- Where date is null

)

))