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,
I am having issue with Power BI report. The datatsource is SQL server where data is loaded as cumulative total. But My goal is to alculate monthly based on cumulative total.
Please see data sample below (Posted date is a calculated column in PBI and it's ralted to Calendar table).
FiscalYear | AccountingPeriod | Scenario | PeriodType | Amount | Posted date |
2021 | 1 | Actual | YTD | $100 | 1/31/2021 |
2021 | 2 | Actual | YTD | $200 | 2/28/2021 |
2021 | 3 | Actual | YTD | $300 | 3/31/2021 |
Your help is much appricicated.
Thank you in advance!
ISGirl
Solved! Go to Solution.
@ISGirl Are the amounts in your table already cumulative amounts (i.e. there is no column that specifies the amount that each month had against it)? If you have a monthly amount, you should be able to just drag the Amount field into a visual with the Period field without any measure.
I may be misunderstanding what you are after? Apologies on my end.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
I am so sorry for the misunderstanding! That is my bad. This should help
Monthly Amount = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] ) - CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )
The above can be broken down using variables as follows:
Monthly Amount =
VAR _TotalMth = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] )
VAR _TotalLstMth = CALCULATE( [Sum Amount] , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )
RETURN
_TotalMth - _TotalLstMth
Hope this helps and apologies again!
Theo 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@ISGirl -
You might need some more tweaking if you have additional data but the example could serve as a template
Distributed Amount =
DIVIDE(
CALCULATE(
MAX(TableName[Amount]),
FILTER(
ALL(TableName),
TableName[FiscalYear]
)
),
CALCULATE(
COUNTROWS(TableName),
FILTER(
ALL(TableName),
TableName[FiscalYear]
)
)
)
Proud to be a Super User!
Hi @ISGirl,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@ISGirl -
You might need some more tweaking if you have additional data but the example could serve as a template
Distributed Amount =
DIVIDE(
CALCULATE(
MAX(TableName[Amount]),
FILTER(
ALL(TableName),
TableName[FiscalYear]
)
),
CALCULATE(
COUNTROWS(TableName),
FILTER(
ALL(TableName),
TableName[FiscalYear]
)
)
)
Proud to be a Super User!
Hi @ISGirl
Please use the below measure:
Cumulative Sum = TOTALYTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] )
Hope this helps 🙂
Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
I need calculation that would return the oppoiste result 🙂
Jan =$100
Feb=$100
Mar=$100
@ISGirl my apologies, you should just be able to drag the Amount column without a measure if you are just after the monthly amount total?
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
It still showing cumulative. Thank you so much for your help!
@ISGirl Are the amounts in your table already cumulative amounts (i.e. there is no column that specifies the amount that each month had against it)? If you have a monthly amount, you should be able to just drag the Amount field into a visual with the Period field without any measure.
I may be misunderstanding what you are after? Apologies on my end.
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
I don't have monthly amount (only for January), the rest of months are cumulative. So I need formula calculating monthly amount based on cumulative.
I am so sorry for the misunderstanding! That is my bad. This should help
Monthly Amount = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] ) - CALCULATE ( SUM ( 'Table'[Amount] ) , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )
The above can be broken down using variables as follows:
Monthly Amount =
VAR _TotalMth = TOTALMTD ( SUM ( 'Table'[Amount] ) , 'Table'[Posted Date] )
VAR _TotalLstMth = CALCULATE( [Sum Amount] , DATEADD ( 'Table'[Posted Date] , -1 , MONTH ) , ALL ( 'Table' ) )
RETURN
_TotalMth - _TotalLstMth
Hope this helps and apologies again!
Theo 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
@ISGirl can you kindly accept the solution I put forward as "solution" so as to close the topic? Many thanks! Theo
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
Thank you so much! It works perfectly fine for 2021. But if I have 2019 and 2020, do you think it will work?
Hi @ISGirl, absolutely 🙂
If I have posted a response that resolves your question, please accept it as a solution to formally close the post.
Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!
Want to connect?www.linkedin.com/in/theoconias
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |