Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
I’m trying to create a cumulative measure in Power BI to count the active products by fiscal year. Here's the setup:
- Calendar Table: Contains dates and fiscal years, including fiscalyearstartdate
- Product Table: Contains ProductID, IntroDate (year introduced as FiscalYearStartDate), and EOLDate (EOL date as FiscalYearStartDate).
Theres an active relationship betwen ProductTable[IntroDate] and Calendar[FiscalYearStartDate] and also and inactive relationship between ProductTable[EOL] and Calendar[FiscalYearStartDate]
Problem:
I want to count the number of active products up to each fiscal year. A product is active if it was introduced before or during the fiscal year and either has no EOLDate or its EOLDate is after the fiscal year.
The measure I’m using counts products for the selected year but doesn't accumulate over time.
CountActiveProducts =
VAR CurrentFiscalYear = ('Calendar'[FiscalYearStartDate])
RETURN
CALCULATE(
DISTINCTCOUNT('Mapping Supplies'[SKU]),
FILTER('Mapping Supplies',
CurrentFiscalYear>='Mapping Supplies'[IntroDate]&&CurrentFiscalYear<'Mapping Supplies[EOLDate]'
)
The result is not cumulative, and only counts the products introduced for the selected fiscal year, not the total up to that year and ignoring the EOL dates.
Any suggestions on how to fix this? Appreciate your help!
Solved! Go to Solution.
Try
Count active products =
VAR CurrentDate =
MAX ( 'Calendar'[FiscalYearStartDate] )
VAR Result =
CALCULATE (
DISTINCTCOUNT ( 'Mapping Supplies'[SKU] ),
'Mapping Supplies'[IntroDate] <= CurrentDate
&& (
ISBLANK ( 'Mapping Supplies[EOLDate]' )
|| 'Mapping Supplies[EOLDate]' > CurrentDate
),
REMOVEFILTERS ( 'Calendar' )
)
RETURN
Result
Try
Count active products =
VAR CurrentDate =
MAX ( 'Calendar'[FiscalYearStartDate] )
VAR Result =
CALCULATE (
DISTINCTCOUNT ( 'Mapping Supplies'[SKU] ),
'Mapping Supplies'[IntroDate] <= CurrentDate
&& (
ISBLANK ( 'Mapping Supplies[EOLDate]' )
|| 'Mapping Supplies[EOLDate]' > CurrentDate
),
REMOVEFILTERS ( 'Calendar' )
)
RETURN
Result
User | Count |
---|---|
13 | |
10 | |
8 | |
7 | |
5 |
User | Count |
---|---|
24 | |
16 | |
15 | |
10 | |
7 |