Issue in using FIRSTDATE and MIN &MINX in DAX measure

[rohithkothaneth]

Hello,

I'm trying to find the StartDate and EndDate selected using the date slicer which is looking at a Data Table.  Use this date in a DAX measure to find the count of case numbers based on case type.

Below two columns are added to the same table based on the below formula

DateOpened = [Opened At].[Date]

DateClosed = [Closed At].[Date]

Here is my sample data:

Type	Opened At	Closed At
Type 1	1/19/2021 17:51
Type 2	1/5/2022 10:22	1/5/2022 12:22
Type 3	1/18/2022 19:38
Type 2	7/19/2022 7:35	7/20/2022 11:26
Type 3	11/23/2022 23:37	11/24/2022 11:28
Type 3	3/9/2023 0:49	3/10/2023 11:34
Type 1	9/19/2022 1:25	9/20/2022 16:53
Type 2	1/31/2023 4:58
Type 3	11/7/2022 6:04
Type 2	4/8/2019 8:23	4/8/2019 0:23
Type 1	12/28/2021 1:36	12/28/2021 10:36
Type 1	4/16/2019 17:52	4/16/2019 22:52

Below is my measure which is returning blank rows

Beginning Balance =

var thisType = "Type 2"

var thisPeriodBegin = FIRSTDATE('_Dates'[Date])

var thisPeriodEnd = LASTDATE('_Dates'[Date])

var filtered = FILTER('Cases',

'Cases'[Type] = thisType

&&

('Cases'[DateOpened] < thisPeriodBegin)

&&

('Cases'[DateClosed] = blank() || 'Cases'[DateClosed] >= thisPeriodBegin)

)

var total = FILTER('Cases',

('Cases'[DateOpened] < thisPeriodBegin)

&&

('Cases'[DateClosed] = blank() || 'Cases'[DateClosed] >= thisPeriodBegin)

)

return if( (thisType = "Total"),

CALCULATE(COUNTROWS(total)),

CALCULATE(COUNTROWS(filtered))

)

I'm getting issue with the FIRSTDATE calculation, though if I try to return the result of the firstdate in a card visual, it is returning the correct firstdate selected per the date slicer. I tried MIN & MINX instead of FIRSTDATE and I'm getting the same result as BLANK ROW.

Tried to comment the ('Cases'[DateOpened] < thisPeriodBegin)  && within the "Filtered" & ('Cases'[DateOpened] < thisPeriodBegin)  && within the "Total" veriables and then Im getting the results as I needed.  

Did I missed anything here? Any help in getting this issue resolved is really appreciated.

Thanks,

Rohith 

[rohithkothaneth]

Unfortunately Im not able to find anything in the onedrive. I believe this could be a restriction on my client laptop.  Please see the screenshot below.

Anyways I was able to find a solution to my issue, the filter on the date table was restricting to look date less than the selected date slicer data.  Since I couldn't download the file, not sure if that was the approach you took in your pbix file.

Beginning Balance =

var thisType = "Type 2"

var thisPeriodBegin = FIRSTDATE('_Dates'[Date])

var thisPeriodEnd = LASTDATE('_Dates'[Date])

RETURN if( (thisType = "Total"),

CALCULATE(COUNTROWS(

FILTER('Cases',

'Cases'[DateOpened] < thisPeriodBegin

&&

('Cases'[DateClosed] = blank() || 'Cases'[DateClosed] >= thisPeriodBegin )

)

), REMOVEFILTERS('_Date Opened')

),

CALCULATE(COUNTROWS(

FILTER( 'Cases',

'Cases'[Type] = thisType

&&

('Cases'[DateOpened] < thisPeriodBegin)

&&

('Cases'[DateClosed] = blank() || 'Cases'[DateClosed] >= thisPeriodBegin)

)

), REMOVEFILTERS ('_Date Opened')

)

)

[rohithkothaneth]

Thank you @Ashish_Mathur for youe response.  Unfourtunately i'm not able to download the PBI file form the onedrive. When I opened the link the drive is empty.

Could you please show me the chnages at the measure that worked for you?

[Ashish_Mathur]

Hi,

The file is there.  See the screenshot.  There are many steps so cannot write the down.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.