In SSAS I have the fact table:
| ContractID | DateFrom | DateTo |
|---|---|---|
| 1001 | 2024-06-01 | 2024-06-20 |
| 1002 | 2024-06-05 | 2024-06-10 |
| 1002 | 2024-06-15 | 2024-07-10 |
| 1003 | 2024-06-20 | 2024-08-05 |
and Date dimention:
| Year | MonthName | MonthId | Date |
|---|---|---|---|
| 2024 | June | 6 | 2024-06-01 |
| 2024 | June | 6 | 2024-06-02 |
…
There is no relationship between the fact table and Date dimention.
I need to create a measure with a unique count of ContractID, where Date value (Date dimention) between DateFrom and DateTo (fact table). The measure should work when choosing any date level from Date dimention: year, year-month, year-month-date.
CountOfContracts :=
CALCULATE(
DISTINCTCOUNT('FactTable'[ContractId]),
FILTER('FactTable',
(
SELECTEDVALUE('DateDimention'[Date]) >= 'FactTable'[DateFrom] &&
SELECTEDVALUE('DateDimention'[Date]) <= 'FactTable'[DateTo]
)
)
)
Problem: this doesn’t work with date at month and year level
2