Calculate a date field based on a filter

[jayPBI23]

If a user selects this Settled Date range,

I want the CR001_Reporting_Period_Start_Date to match '6/1/2020' &

CR001_Reporting_Period_Start_Date to match '6/30/2020' 

So pretty much dynaically update the field to whatever the user selected in the filter. Is this possible?

Thanks for any help in advance!

Jay

[v-alq-msft · Accepted solution]

Hi, @jayPBI23 

Based on your description, I created datat to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDAR(DATE(2020,6,1),DATE(2020,12,31))

There is no relationship between two tables. You may create two measures as below.

CR001_Reporting_Period_Start_Date = 
CALCULATE(
    MIN('Calendar'[Settled Date]),
    ALLSELECTED('Calendar'[Settled Date])
)

CR001_Reporting_Period_End_Date = 
CALCULATE(
    MAX('Calendar'[Settled Date]),
    ALLSELECTED('Calendar'[Settled Date])
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jayPBI23 , Try like

measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return calculate(sum(Table[column]), filter(Allselected(Table),Table[CR001_Reporting_Period_Start_Date]=_min && Table[CR001_Reporting_Period_Start_Date]=_max))

measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return calculate(sum(Table[column]), all(Table[Settled Date]),filter(Table,Table[CR001_Reporting_Period_Start_Date]=_min && Table[CR001_Reporting_Period_Start_Date]=_max))

[jayPBI23]

Hi Amit. 

I don't use Dax as much. Trying to use your code keeps erroring out for me. Here are some details. 

Table = 'Query1'

Date slicer field = 'SettledDate' 

CR0001_Reporting_Period_start_Date should be '6-1-2020' based on the settled date filter

CR0002_Reporting_Period_End_Date should be '6-30-2020' based on the settled date filter

What exactly does the measure need to be?

Thanks a bunch!

Jay

[camargos88]

Hi @jayPBI23,

You are gonna need 2 measures here, getting min and max of the selected date, something like:

Measure1 = 

VAR _dtStart = MINX(TABLE[DATE], [DATE])

RETURN CALCULATE(SUM(TABLE[VALUES]), FILTER(ALL(TABLE([DATE]), [DATE] = _dt_Start))

Measure1 = 

VAR _dtEnd= MAXX(TABLE[DATE], [DATE])

RETURN CALCULATE(SUM(TABLE[VALUES]), FILTER(ALL(TABLE([DATE]), [DATE] = _dtEnd))

[jayPBI23]

I don't use Dax as much. Trying to use your code keeps erroring out for me. Here are some details. 

Table = 'Query1'

Date slicer field = 'SettledDate' 

CR0001_Reporting_Period_start_Date should be '6-1-2020' based on the settled date filter

CR0002_Reporting_Period_End_Date should be '6-30-2020' based on the settled date filter

What exactly does the measure need to be?

Thanks a bunch!

Jay