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.
Hello Everyone,
I'm dealing with following scenario.
We have table with following structure:
What I would like to do is:
Calculate accumulative total for POS_AMT measure based on WEEK_END_DATE.
I did that using following formula:
POS_AMT_CUMULATIVE = CALCULATE( SUM(ARD_POS_FRCST_WKLY[POS_AMT]); FILTER( ALLEXCEPT(ARD_POS_FRCST_WKLY; ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME];ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]); ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX(ARD_POS_FRCST_WKLY[WEEK_END_DATE]) ) )
However, at certain point, POS_AMT values for few weeks are Blanks, with just FORECAST_AMT column being filed.
What I would want to achieve is -> For WEEK_END_DATE values that have FORECAST_AMT, but doesn't have POS_AMT use FORECAST_AMT insead of POS_AMT in cumulative calculation.
The other way would be to calculate cumulative POS_AMT for only those weeks that have such values and later on calculate another measure with FORECAST_AMT, but only for those weeks that doesn't have POS_AMT.
Hope it's clear enough... 🙂
Solved! Go to Solution.
Hi,
This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.
Column = IF ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] = CALCULATE ( MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ), FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 ) ), 1, 0 )
Best Regards!
Dale
Hi,
You just need to change SUM into SUMX. Try this one below.
POS_AMT_CUMULATIVE =
CALCULATE (
SUMX (
ARD_POS_FRCST_WKLY,
IF (
ARD_POS_FRCST_WKLY[POS_AMT] = 0,
ARD_POS_FRCST_WKLY[FORECAST_AMT],
ARD_POS_FRCST_WKLY[POS_AMT]
)
),
FILTER (
ALLEXCEPT (
ARD_POS_FRCST_WKLY,
ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME],
ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]
),
ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )
)
)
Thank you!
That actually is working almost as expected.
"Almost", because our "ideal" scenario, would be to have those values that are using [FORECAST_AMT} column to be colored separately. Is it doable though?
Hi,
We can’t color them separately due to we accumulate the total. If the first month uses FROCAST_AMT, we can say the last month uses it too. If we want to find out the current month using it or not, we can add a calculated column, and then add it to legend. This doesn’t work with area chart. You can have a look at the image below. The black column means this column uses FROCAST_AMT. Is this what you want?
UsingFrocast =
CALCULATE (
IF ( MIN ( ARD_POS_FRCST_WKLY[POS_AMT] ) = 0, 1, 0 ),
FILTER (
ARD_POS_FRCST_WKLY,
MONTH ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )
= MONTH ( EARLIER ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ) )
)
)
Best regards
Dale
@v-jiascu-msftUnfortunately not :(.
Our goal is to show both accumulative POS_AMT and FORECAST_AMT on 1 chart (preferably Area Chart or Line Chart), with the same idea as before so:
If WEEK_END_DATE has POS_AMT show POS_AMT otherwise show FORECAST_AMT, or by any chance "mark" the week in which we're starting to use FORECAST_AMT.
Hi,
This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.
Column = IF ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] = CALCULATE ( MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ), FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 ) ), 1, 0 )
Best Regards!
Dale
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 |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |