Hi Guys
I have created this Dax calculated Date Table:
DateTable =
VAR MaxDate = TODAY()
VAR CalendarTable = CALENDAR(DATE(2022, 1, 1), MaxDate)
RETURN
ADDCOLUMNS (
CalendarTable,
"DateText", FORMAT([Date], "ddmmyyyy"),
"Year", YEAR([Date]),
"Week", WEEKNUM([Date], 21),
"Month", FORMAT([Date], "mmmm"),
"Month No.”, MONTH([Date]),
"Quarter", FORMAT([Date], "\QQ"),
"YTD", IF(YEAR([Date]) = YEAR(MaxDate), 1, 0),
"Today", IF([Date] = MaxDate, 1, 0),
"MonthYear", FORMAT([Date], "mm-yyyy"),
"CurrentYear",
IF(
YEAR([Date]) = YEAR(MaxDate),
"CurrentYear",
FORMAT(YEAR([Date]), "0")
),
"CurrentMonth",
IF(
MONTH([Date]) = MONTH(MaxDate),
"CurrentMonth",
FORMAT([Date], "MMM")
),
"ClosedDates",
IF(
LOOKUPVALUE('Finance'[DateClosed], 'Finance'[Date], [Date]) = TRUE(),
FORMAT([Date], "MMMM"),
BLANK()
),
"Sort By",
IF(
LOOKUPVALUE('Finance'[DateClosed], 'Finance'[Date], [Date]) = TRUE(),
FORMAT([Date], "MM-YYYY"),
""
),
"Index",
RANKX(FILTER(CalendarTable, [Date] <= EOMONTH(MaxDate, -2)), [Date], , ASC, Dense)
)
And another Dax calculated Date table, which is;
DateTableFilter =
VAR MaxDate = TODAY()
VAR CalendarTable = CALENDAR(DATE(2022, 1, 1), MaxDate)
RETURN
ADDCOLUMNS (
FILTER(CalendarTable,
NOT(ISBLANK(
LOOKUPVALUE('Finance'[DateClosed], 'Finance'[Date], [Date])
)) &&
LOOKUPVALUE('Finance'[DateClosed], 'Finance'[Date], [Date]) <> FALSE()
),
"DateText", FORMAT([Date], "ddmmyyyy"),
"Year", YEAR([Date]),
"Week", WEEKNUM([Date], 21),
"Month", FORMAT([Date], "mmmm"),
"Month No.", MONTH([Date]),
"Quater", FORMAT([Date], "\QQ"),
"YTD", IF(YEAR([Date]) = YEAR(MaxDate), 1, 0),
"Today", IF([Date] = MaxDate, 1, 0),
"Monthyear", FORMAT([Date], "mm-yyyy"),
"CurrentYear",
IF(
YEAR([Date]) = YEAR(MaxDate),
"CurrentYear",
FORMAT(YEAR([Date]), "0")
),
"ClosedDates",
FORMAT([Date], "MMMM"),
"Sort By",
FORMAT([Date], "MM-YYYY"),
"Index",
RANKX(FILTER(CalendarTable, [Date] <= EOMONTH(MaxDate, -2)), [Date], , ASC, Dense)
)
The tables have the following relationships:
DateTableFilter (1-to-Many) -> DateTable
DateTable (1-to-Many) -> Finance
in the DateTableFilter table I have a measure;
ClosedDatesNoFilter =
MAXX(
VALUES(DateTableFilter[ClosedDates]),
DateTableFilter[ClosedDates]
)
This measure I’d like to use for a Card visual on a report page. Though the measure returns the correct value, but on the same page I have a month filter made up of the ClosedDates column in the same table, and when a value is selected in the month filter the Card value changes accordingly which is not what I’m wishing for. I’ve even tried removing the interaction between the month filter [ClosedDates] and the Card visual [ClosedDatesNoFilter] but that just messes with the Card value, and returns another date than expected.
What am I missing or doing wrong here?
