If I write Calculate with filter, create a visual card and select date, it shows correct count
Num_SelectDetailsRows =
VAR select_date = SELECTEDVALUE(EomDates[Date])
VAR filt_headertbl = CALCULATETABLE(VALUES(HeaderTbl[HeaderKey]), FILTER(HeaderTbl, [ValidFrom] <= select_date && [ValidTo] >= select_date))
VAR filt_detailstbl = CALCULATE(COUNT(DetailsTbl[DetailsKey]), DetailsTbl[HeaderKey] in filt_headertbl)
RETURN
filt_detailstbl
However, if i convert it into a table and show on visual it shows empty
Table =
VAR select_date = SELECTEDVALUE(EomDates[Date])
VAR filt_headertbl = CALCULATETABLE(VALUES(HeaderTbl[HeaderKey]), FILTER(HeaderTbl, [ValidFrom] <= select_date && [ValidTo] >= select_date))
VAR filt_detailstbl = CALCULATETABLE(DetailsTbl, DetailsTbl[HeaderKey] in filt_headertbl)
RETURN
filt_detailstbl
Sample data is
HeaderTabl
| HeaderTbl |
| HeaderKey | ValidFrom | ValidTo |
| H1 | 1Jan2022 | 31Dec2022 |
| H2 | 15Feb2022 | 31Oct2022 |
| H3 | 10Mar2022 | 15Sep2022 |
| H_FebOnly | 7Feb2022 | 25Feb2022 |
| H_FebMar | 25Feb2022 | 14Apr2022 |
| DetailsTbl |
| DetailsKey | HeaderKey | Name | Currency |
| D1 | H1 | A | EUR |
| D2 | H2 | B | GBP |
| D3 | H3 | C | AUD |
| D4 | H_FebOnly | D | GBP |
| D5 | H_FebMar | E | USD |
Dates I try in slicer
Date
| 31 Dec 2021 |
| 31 Jan 2022 |
| 28 Feb 2022 |
| 31 Mar 2022 |
| 30 Apr 2022 |
| 31 May 2022 |
| 30 Jun 2022 |
| 31 Jul 2022 |
| 31 Aug 2022 |
| 30 Sep 2022 |
| 31 Oct 2022 |
| 30 Nov 2022 |
What am I missing?
