Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Power bi community,
I have the following data: MONTH_ID, PRODUCT, YEAR AND ID_CODE.
I would like to create a measure that would result in a column with the same behavior as the column "RESULT":
I tried to create a measure, but doesn't have the result expect.
Result = CALCULATE([TOTAL],
FILTER(
ALL('TABLE1'[MONTH_ID]),
'TABLE1'[MONTH_ID] < MAX('TABLE1'[MONTH_ID]))
The measure must be the value up to the previous month of the filter, that is, if no filter is defined MONTH_ID = 10, the cumulative result must be up to MONTH_ID = 9
Solved! Go to Solution.
Hi @iago_salvadori1 ,
Here I have a test by your sample data. I think you can use "less or equal to" function in your filter and you can achieve your goal.
Quantity CUMULATIVE (Category 10) =
CALCULATE (
[Quantity MONTHLY (Category 10)],
FILTER (
ALL ( Table1[ID_MONTH] ),
Table1[ID_MONTH]
<= MAX ( Table1[ID_MONTH] ) - 1
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@iago_salvadori1 , Not able to related total and result,
You need like
Result = CALCULATE([TOTAL],
FILTER(
ALL('TABLE1'[MONTH_ID]),
'TABLE1'[MONTH_ID] < MAX('TABLE1'[MONTH_ID]) - 1 )
Or have year month table/ date
new column , in a new month year table
Month Rank = RANKX(all('Date'),'Date'[MMonth Year],,ASC,Dense)
The try measure like
Result = CALCULATE([TOTAL],
FILTER(
ALL('Date'),
'Date'[Month Rank] < MAX('TABLE1'[Month Rank]) - 1 )
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hello @amitchandak, thanks your suggestion.
I don't know how to send Pbix here, but I'll send the table with the data:
ID_COMPANY | ID_CATEGORY | ID_MONTH | PRODUCT | YEAR | CLIENT | QTD |
5600 | 10 | 1 | Product A | 2021 | 1105 | 40 |
5400 | 10 | 1 | Product B | 2021 | 1001 | 30 |
5600 | 10 | 2 | Product C | 2021 | 1001 | 30 |
5600 | 10 | 3 | Product A | 2021 | 1001 | 10 |
5400 | 10 | 3 | Product A | 2021 | 1105 | 40 |
5400 | 10 | 4 | Product A | 2021 | 1105 | 50 |
5400 | 10 | 5 | Product A | 2021 | 1001 | 10 |
5600 | 10 | 6 | Product C | 2021 | 1002 | 5 |
5600 | 10 | 7 | Product C | 2021 | 1105 | 5 |
5600 | 10 | 8 | Product B | 2021 | 1107 | 100 |
5400 | 10 | 10 | Product D | 2021 | 1105 | 30 |
5500 | 10 | 9 | Product B | 2021 | 1105 | 10 |
5500 | 10 | 11 | Product Z | 2021 | 1001 | 30 |
5400 | 10 | 11 | Product A | 2021 | 1105 | 25 |
5600 | 20 | 3 | Product Z | 2021 | 1001 | 20 |
5600 | 20 | 4 | Product A | 2021 | 1105 | 60 |
5400 | 10 | 12 | Product B | 2021 | 1107 | 30 |
5500 | 10 | 7 | Product C | 2021 | 1001 | 10 |
I create two measures:
Quantity MONTHLY (Category 10) =
CALCULATE(
SUMX(Table1,Table[QTD]),Table1[ID_CATEGORY] = 10)
Quantity CUMULATIVE (Category 10) =
CALCULATE([Quantity MONTHLY (Category 10)],
FILTER(
ALL(Table1[ID_MONTH]),
Table1[ID_MONTH] < MAX(Table1[ID_MONTH]) - 1))
With the following filters in the chart:
Graph result:
The measure CUMULATIVE Quantity (Category 10) has the wrong result 330, It should be 340.
Hi @iago_salvadori1 ,
Here I have a test by your sample data. I think you can use "less or equal to" function in your filter and you can achieve your goal.
Quantity CUMULATIVE (Category 10) =
CALCULATE (
[Quantity MONTHLY (Category 10)],
FILTER (
ALL ( Table1[ID_MONTH] ),
Table1[ID_MONTH]
<= MAX ( Table1[ID_MONTH] ) - 1
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |