I have 3 measures to calculate active merchants in a given periods using DAX time intelligence. In addition to that, I need to also check that their first transaction (in FactTransaction) is in current relative year
Example:
For the New Active Merchants LM:
Take all the Active Merchants LM#
For these merchants, eliminate all those who do not have their first transaction/invoice in the current fiscal year (if calculated for any month in 2023 check their first transaction is in 2023, etc)
// base measure
# Merchant =
CALCULATE(
DISTINCTCOUNTNOBLANK(DimMerchant[MerchantHK]),
FactTransaction
)
// Last Month or Previous Month
# Active Merchants LM# = CALCULATE([# Merchant], PREVIOUSMONTH(DimDate[DateValue]))
// Last 3 months
# Active Merchants L3M# =
CALCULATE(
[# Merchant],
DATESINPERIOD(DimDate[DateValue], MAX(DimDate[DateValue]), -3, MONTH)
)
// Last 12 months
# Active Merchants LY# =
CALCULATE(
[# Merchant],
DATESINPERIOD(DimDate[DateValue], MAX(DimDate[DateValue]), -12, MONTH)
)
Table relationship:
Sample data:
- DimDate (one to many to FactTransaction on DatePK)
DatePK | DateValue | Year | Month |
---|---|---|---|
20230901 | 9/1/2023 | 2023 | 9 |
20231001 | 10/1/2023 | 2023 | 10 |
20231101 | 11/1/2023 | 2023 | 11 |
20231201 | 12/1/2023 | 2023 | 12 |
20240101 | 1/1/2024 | 2024 | 1 |
20240201 | 2/1/2024 | 2024 | 2 |
20240301 | 3/1/2024 | 2024 | 3 |
20240401 | 4/1/2024 | 2024 | 4 |
20240501 | 5/1/2024 | 2024 | 5 |
- DimMerchant (one to many to FactTransaction on MerchantHK)
MerchantHK | MerchantName |
---|---|
AAA | NAAA |
AAA | NAAA |
BBB | NBBB |
DDD | NDDD |
BBB | NBBB |
DDD | NDDD |
CCC | MCCC |
DDD | MDDD |
BBB | MBBB |
- FactTransaction (Main fact table where DimDate and DimMerchant have relationship)
DatePK | MerchantHK |
---|---|
20230901 | AAA |
20231001 | AAA |
20231101 | BBB |
20231201 | DDD |
20240101 | BBB |
20240201 | DDD |
20240301 | CCC |
20240401 | DDD |
20240501 | BBB |