Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |