Use measure as a filter in DAX

[smathur12]

Hi,

I have a dax which should return a date. I want it to get filtered by a measure value.

68 in the dax needs to be replaced by a measure. The moment I put the measure there, it returns BLANK. And when it's hard coded to a value, it returns correct result. Any help here would be highly appreciated. Thank You.

Start of previous quarter =

CALCULATE(DISTINCT(Mytable[QTR_START_DATE]),

CALCULATETABLE(ALL(Mytable)),

(Mytable[QTR_SEQ_NUMBER]= 68))

[Greg_Deckler · Accepted solution]

@smathur12 Try this and then you can more easily troubleshoot what is going wrong by returning the variables (use TOCSV for the table variables).

Measure = 
  VAR __FilterMeasure = [FilterMeasure]
  VAR __Table = FILTER(ALL('MyTable'), [QTR_SEQ_NUMBER] = __FilterMeasure)
  VAR __Dates = DISTINCT(SELECTCOLUMNS(__Table, "__Date", [QTR_START_DATE]))
  VAR __Result = MAXX(__Dates, [__Date])
RETURN
  __Result

[smathur12]

I was not putting the variables in the right way. This method worked. Thanks a lot Greg. Saved my day.

[smathur12]

Thanks Greg. I did try this and it's also returning a date but not correct. I'll give some more details here. My dataset has multiple rows with period and quarter information. I need to find Quarter to Quarter Sales, which means I need Current quarter start date, current quarter end date, previous quarter end date and previous quarter start date. Previous Quarter Start date is the trickiest because user can select multiple periods. Attaching the date format in the table. So if someone selects qtr 202303 and 202302 - then previous quarter start date should be 10/9/2022. The DAX needs to return this date.

PERIOD	YR_QTR	QTR_START_DATE	QTR_END_DATE	PREV_QTR_START_DATE	PREV_QTR_END_DATE
202310	202303	7/16/2023	10/7/2023	4/23/2023	7/15/2023
202309	202303	7/16/2023	10/7/2023	4/23/2023	7/15/2023
202308	202303	7/16/2023	10/7/2023	4/23/2023	7/15/2023
202307	202302	4/23/2023	7/15/2023	1/1/2023	4/22/2023
202306	202302	4/23/2023	7/15/2023	1/1/2023	4/22/2023
202305	202302	4/23/2023	7/15/2023	1/1/2023	4/22/2023
202304	202301	1/1/2023	4/22/2023	10/9/2022	12/31/2022
202303	202301	1/1/2023	4/22/2023	10/9/2022	12/31/2022
202302	202301	1/1/2023	4/22/2023	10/9/2022	12/31/2022
202301	202301	1/1/2023	4/22/2023	10/9/2022	12/31/2022
202213	202204	10/9/2022	12/31/2022	7/17/2022	10/8/2022
202212	202204	10/9/2022	12/31/2022	7/17/2022	10/8/2022
202211	202204	10/9/2022	12/31/2022	7/17/2022	10/8/2022
202210	202203	7/17/2022	10/8/2022	4/24/2022	7/16/2022
202209	202203	7/17/2022	10/8/2022	4/24/2022	7/16/2022
202208	202203	7/17/2022	10/8/2022	4/24/2022	7/16/2022
202207	202202	4/24/2022	7/16/2022	1/2/2022	4/23/2022
202206	202202	4/24/2022	7/16/2022	1/2/2022	4/23/2022
202205	202202	4/24/2022	7/16/2022	1/2/2022	4/23/2022

[smathur12]

The format of the table in my previous message did not come proper. Here is a snapshot for your reference.