Forum Discussion
transform the data so that the yearly value is broken down by each month in the start and e
I want to create a DAX functiont to transform the data so that the yearly value is broken down by each month in the start and end periods.
For example, if the start period is 01 March 2022 and the end period is 01 May 2023, the yearly value should be broken down into equal parts monthly values for March to December in 2022 and January toMay in 2023.
spans over a period of 13 months (Apr-22 to Apr-23)
Year 2022 has 9 months (April to December)
Year 2023 has 4 months (January to April)
Part 1: Display the yearly value for each initiative.
Example:
Initiative No. 1489 spans over a period of 13 months (Apr-22 to Apr-23)
Year 2022 has 9 months (April to December) = 100 * 9 = 900
Year 2023 has 4 months (January to April) = 100* 4 = 400
Part 2: Add a new column for the delta savings.
Example - for initiative 1489.
2021 delta = 0 (since initiative starts in 2022)
2022 delta = 900 – 0 = 900
2023 delta = 400 – 900 = -500
2024 delta = 0-400 = -400
1 Reply
- AnonymousNot applicable
Hi ankitatiwari620 ,
Could you please provide some source data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
In addition, you can refer the following info to split the data into monthly/yearly data:
Split Amount Across Months Using Power Query - Microsoft Fabric Community
Split Amount across Months Using Power Query in Excel and Power BI – ExcelFort
powerbi - Spilt Revenue By Percentages in right year buckets in Power BI - Stack Overflow
Best Regards