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.
Hi,
am very new to power bi.
i have Matrix in my .pbix like this,
and i want to calculate rolling cost, and final result should be like this,
Any help on this is much appreciated.
Thanks in Advance.
Solved! Go to Solution.
You can use this function to get the cumulative.
I am sharing the PBIX which you can download form here
If you find this as solution please mentione this as solution and share your Kudos.
Also you can watch my Videos on www.youtube.com/perepavijay
Regards
Vijay Perepa
Proud to be a Super User!
Hi @yodha ,
You can follow these steps to try:
1. I have created a calculated column to fomat date column for easy calculation:
Month-Year = VALUE(FORMAT('Table'[Date],"yyyymm"))
2. I have created a measure to calculate present cost to replace previous column because I thought it was contradictory when calculating total cost basd on previous month:
Present cost measure =
VAR _minmonthyear =
CALCULATE ( MIN ( 'Table'[Month-Year] ), ALLSELECTED ( 'Table' ) )
VAR _cmonthyear =
SELECTEDVALUE ( 'Table'[Month-Year] )
VAR _lastmonthyear =
CALCULATE (
MAX ( 'Table'[Month-Year] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month-Year] < _cmonthyear )
)
RETURN
IF (
_cmonthyear = _minmonthyear,
SUM ( 'Table'[Present cost] ),
CALCULATE (
SUM ( 'Table'[Present cost] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Categoary] ),
'Table'[Month-Year] = _minmonthyear
)
)
- CALCULATE (
SUM ( 'Table'[Forecast cost] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Categoary] ),
'Table'[Month-Year] < _cmonthyear
)
)
)
3. Finally, create a measure to calculate total cost:
Total cost = [Present cost measure]-SUM('Table'[Forecast cost])
4. You will get the result like this:
Here is my sample file that hopes to help you, please try it: PBIX
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @yodha ,
You can follow these steps to try:
1. I have created a calculated column to fomat date column for easy calculation:
Month-Year = VALUE(FORMAT('Table'[Date],"yyyymm"))
2. I have created a measure to calculate present cost to replace previous column because I thought it was contradictory when calculating total cost basd on previous month:
Present cost measure =
VAR _minmonthyear =
CALCULATE ( MIN ( 'Table'[Month-Year] ), ALLSELECTED ( 'Table' ) )
VAR _cmonthyear =
SELECTEDVALUE ( 'Table'[Month-Year] )
VAR _lastmonthyear =
CALCULATE (
MAX ( 'Table'[Month-Year] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month-Year] < _cmonthyear )
)
RETURN
IF (
_cmonthyear = _minmonthyear,
SUM ( 'Table'[Present cost] ),
CALCULATE (
SUM ( 'Table'[Present cost] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Categoary] ),
'Table'[Month-Year] = _minmonthyear
)
)
- CALCULATE (
SUM ( 'Table'[Forecast cost] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Categoary] ),
'Table'[Month-Year] < _cmonthyear
)
)
)
3. Finally, create a measure to calculate total cost:
Total cost = [Present cost measure]-SUM('Table'[Forecast cost])
4. You will get the result like this:
Here is my sample file that hopes to help you, please try it: PBIX
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
You can use this function to get the cumulative.
I am sharing the PBIX which you can download form here
If you find this as solution please mentione this as solution and share your Kudos.
Also you can watch my Videos on www.youtube.com/perepavijay
Regards
Vijay Perepa
Proud to be a Super User!
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |