Power BI : DAX : count of accounts which have transaction on or before the year

Question

I have three tables: transaction fact table, account dim table and date dim table

'Fact' [Date], [Year], [Account], [Region]

'Account' [Account]

'Date' [Date], [Year], [Month]

I created a Table visualisation in Power BI using 'Date'[Year] as the only dimension. I want to create a measure so that for each row of year, I know the distinct count of accounts which have transaction on or before that year. The table should be allowed to be filtered by the 'Fact'[Region] while unaffected by the filters on Date table

Year	Count of Accounts
2019	1234
2020	2345
2021	3000
2022	4000

I tried the following but not really working

Count of Accounts = 
VAR VAR = 
CALCULATE(
    MIN('Fact'[Year]),
    REMOVEFILTERS('Date')
)     
<= MAX('Date'[Year])

SUMX(
FILTER (
    ADDCOLUMNS (
        CROSSJOIN (
            VALUES ( account[account_name] ),
            VALUES ( 'Date'[Year] )
        ),
        "Result", 1
    ),
    VAR
),[Result]
)

The problem is when there is a year skipped in fact table (no transaction for that account), the measure would return blank. E.g. Account A has transaction in 2020 and 2022. Then it should be counted from 2020 onwards but it is only counted for 2020 and 2022 in my measure.

Appreciate your help in advance.

Answer 1

You just need to consider any filters applied to the 'Fact'[Region] and ignore any filters applied directly to the 'Date' table :

Count of Accounts = 
CALCULATE(
    DISTINCTCOUNT('Fact'[Account]),
    FILTER(
        ALL('Date'), 
        'Date'[Year] <= MAX('Date'[Year])
    ),
    REMOVEFILTERS('Date'),
    ALLSELECTED('Date')
)

The REMOVEFILTERS() will remove filters from the 'Date' table to ensure they don't limit the calculation, while the ALLSELECTED() will keep the visual context and ignore any direct filters on 'Date'.