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
Pieter, thanks for the advice, I was thinking of doing the same, but there is a risk of greatly increasing the size of the fact table. This is how I managed to solve the problem:
CountOfContracts :=
var MonthId = LOOKUPVALUE('DateDimention'[year_month_order/* format YYYYMM */],'DateDimention'[Year], SELECTEDVALUE('DateDimention'[Year]), 'DateDimention'[MonthId], SELECTEDVALUE('DateDimention'[MonthId]))
return
switch
(
true,
HASONEVALUE('DateDimention'[Year]) && not HASONEVALUE('DateDimention'[MonthId]),
CALCULATE(
DISTINCTCOUNT('FactTable'[ContractID]),
FILTER('FactTable',
(
SELECTEDVALUE('DateDimention'[Year]) >= year('FactTable'[DateFrom]) &&
SELECTEDVALUE('DateDimention'[Year]) <= year('FactTable'[DateTo])
)
)
),
HASONEVALUE('DateDimention'[MonthId]) && not HASONEVALUE('DateDimention'[Date]),
CALCULATE(
DISTINCTCOUNT('FactTable'[ContractID]),
FILTER('FactTable',
(
MonthId >= year('FactTable'[StartDate])*100 + month('FactTable'[DateFrom]) &&
MonthId <= year('FactTable'[EndDate])*100 + month('FactTable'[DateTo])
)
)
),
HASONEVALUE('DateDimention'[Date]),
CALCULATE(
DISTINCTCOUNT('FactTable'[ContractID]),
FILTER('FactTable',
(
SELECTEDVALUE('DateDimention'[Date]) >= 'FactTable'[DateFrom] &&
SELECTEDVALUE('DateDimention'[Date]) <= 'FactTable'[DateTo]
)
)
),
BLANK()
)