Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
It doesn't cumulate at all!
COST_PERIOD_COL is a column containing costs. Since the model is a periodic snapshot, it's normal there are voids.
COST_CUM is a measure that gives the expected results but it's slow so I'm aiming to use a column instead. It definition is :
COST_CUM:= IF( [PROJECT_IS_ONGOING]; SUMX( FILTER( ALL(DIM_DATE[THE_DATE]); DIM_DATE[THE_DATE] <= max(DIM_DATE[THE_DATE]) ); FIRSTNONBLANK(FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL];1) ) )
COST_CUM_COL is the goal. It's defined as :
CALCULATE ( SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] ); FILTER( ALL ( DIM_DATE ) ; DIM_DATE[THE_DATE] <= MAX( DIM_DATE[THE_DATE] ) ) )
but doesn't cumulate. Instead it gives the same single value as the column it's reading from.
Another approach (using the fact table instead of the date dimension) gives a very different (but also wrong) result :
This time COST_CUM_COL is defined as :
CALCULATE ( SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] ); FILTER( ALL ( FACT_COST_RESOURCE_CATEGORY ) ; FACT_COST_RESOURCE_CATEGORY[THE_DATE] <= MAX( FACT_COST_RESOURCE_CATEGORY[THE_DATE] ) ) )
I've tried many permutations with or without FILTER, using ALLSELECTED with the tables and columns, EARLIER instead of MAX, etc.
Model has 4 dimensions : DATE, PROJECT (redacted in the screenshots), ACTIVITY ("Achat petits outils" in my example) and RESOURCE_CATEGORY ("Équipement" and "Indéterminé").
HI @6mon,
For cumulative calculate, I think you need to add all/allselected function on fact table to break current filter effect and use calendar date as filter condition:
Column = VAR currDate = MAX ( Dim_date[Date] ) RETURN CALCULATE ( SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] ); FILTER ( ALLSELECTED ( FACT_COST_RESOURCE_CATEGORY ); FACT_COST_RESOURCE_CATEGORY[THE_DATE] <= currDate ); VALUES ( PROJECT[Projecct] ) )
If above not help, can you please share a pbix file with some sample data for test and coding formula?
Regards,
Xiaoxin Sheng
Unfortulately this formula brings me back to 'scenario B' in which a single (total?) value is repeated, across all 4 dimensions, including time. There is no cumulation per se.
I'm begining to believe there's something wrong with model/joins. All the "ID_DIM_*" columns I use in the formulas belong to the fact table (they're the FOREIGN KEYS), should it be otherwise?
The model couldn't be simpler : one fact table that contains a couple of measures and FK to 4 dimensions, one entry per day, and per each dimension (for the days the project is active) in the fact table.
Here's a file containing a representative example : https://totalprojectlogistics-my.sharepoint.com/:u:/g/personal/sbenoit_tpl-solutions_com/Ea_Q4wIJDLF...
I Expect COST_CUM to give 0, 2 and 202 for "Ress a" and 0, 5 and 6 for "Ress b"
Hi @6mon,
You can try to use following formula if it works, I add all columns which you used in matrix visual as conditions:
COST_CUM = CALCULATE ( SUM ( FACT[COST] ), FILTER ( ALLSELECTED ( 'FACT' ), [ID_DIM_PROJECT] = EARLIER ( 'FACT'[ID_DIM_PROJECT] ) && [ID_DIM_RESS_CAT] = EARLIER ( 'FACT'[ID_DIM_RESS_CAT] ) && [ID_DIM_ACTIVITY] = EARLIER ( [ID_DIM_ACTIVITY] ) && FACT[THE_DATE] <= EARLIER ( 'FACT'[THE_DATE] ) ) )
Regards,
Xiaoxin Sheng
Perhaps try:
Column = VAR __date = [THE DATE] RETURN CALCULATE ( SUM ( FACT_COST_RESOURCE_CATEGORY[COST_PERIOD_COL] ); FILTER( ALL ( DIM_DATE ) ; DIM_DATE[THE_DATE] <= __date ) )
No dice, still only gives the period's value 😞
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |