i'm trying to calculate the max ( and min ) of a date.
My situation is the following.
I've create a date table like this:
Date =
ADDCOLUMNS (
CALENDAR (DATE(2008;1;1); DATE(2020;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );"Daynumber"; FORMAT ( [Date]; "DD" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
"WeekNum"; WEEKNUM ( [Date] )
)
I have a table "Table 1" like this :
ID ForeignKey Date
1 A 01/01/2005
2 A 05/04/2008
3 A 31/12/2019
4 B 15/3/2017
5 B 16/05/2018
6 B 15/04/2019
7 C 05/06/2006
8 C 04/12/2015
9 C 15/04/2019
And another table "Table 2" like this
ID2 Price
A 100
B 500
C 650
The "Date" table is related to "Table1" by date and Table 1 is related to "Table 2" by Table1.ForeignKey = Table2.ID2.
In my report i have a date slicer which is set for example to filter dates between Jan 1 2008 and June 30 2018. My goal is to calculate the max and min date for each one of table 2 in the selected period like this:
ID2 Price MinDate MaxDate
A 100 05/04/2008 05/04/2008
B 500 15/3/2017 16/05/2018
C 650 04/12/2015 04/12/2015
All i am able to achieve by doing somethig like this
MaxDate= CALCULATE ( LASTDATE ( Table1[Date] ); FILTER (
ALLSELECTED('Date') ;
'Date'[Date] <= Max('Date'[Date])
))
is the max and min dates in the whole calendar (01/01/2008 - 12/31/2020) , which is not what i'm trying to do .
How can I do this?
Thanks in advance.
