Hi @v-zhangti @lbendlin @Ashish_Mathur
Please excuse me - I jumped into a possible solution, instead I should explain the underlying problem in a good way. I adjusted the files to the actual starting point and reduced the complexity.
I have two files with the following structure
1. Center file
| Project | Project Detail | Sales Recognition Date | Planned Cost | |
| A | A1 | 27.12.2021 | CHF 1’000 | |
| A | A2 | 27.11.2021 | CHF 2’000 | |
| B | B1 | 25.02.2022 | CHF 500 | |
| B | B1a | 25.02.2022 | CHF 1’500 | Amendment |
2. Actual Cost file
| Project | Project Detail | Booking Date | Actual Cost |
| A | A1 | 28.02.2021 | CHF 200 |
| A | A1 | 28.05.2021 | CHF 200 |
| A | A1 | 28.08.2021 | CHF 400 |
| A | A1 | 28.10.2021 | CHF 400 |
| A | A2 | 28.03.2021 | CHF 500 |
| A | A2 | 28.05.2021 | CHF 500 |
| A | A2 | 28.09.2021 | CHF 800 |
| A | A2 | 28.12.2021 | CHF 100 |
| B | B1 | 28.01.2021 | CHF 100 |
| B | B1 | 28.02.2021 | CHF 300 |
| B | B1 | 28.06.2021 | CHF 300 |
| B | B1 | 28.08.2021 | CHF 100 |
| B | B1a | 28.09.2021 | CHF 400 |
| B | B1a | 28.12.2021 | CHF 600 |
| B | B1a | 28.01.2022 | CHF 200 |
To give some context:
Example project A: In project A there are different project stages, A1, A2....
Each stage has planned cost and a planned end date - sales recognition date. The start cost is always 0. The start date is calculated from the first time actual cost have been generated.
From a business perspective the target is to keep as close as possible to the resulting line "Start - End" with the running actual costs - then you are on track to keep the planned budget.
What I would like to achieve is to have a line graph with Actual Costs (Booking Date and Running Total for Actual COst) vs Ideal cost (Start/End Date, 0 Cost/Planned Cost).
It should then look like that, eg for Project A-A1:
This is my main goal. You select project and single project stage with a slicer.
However, to make it a little bit more complicated:
If you realize that the planned budget is insufficient you may request an amendment. This should have the same end date, but a later start date.
To track this you would select two or even more project stages in the same project. If you do so it would be ideal to only display the earliest start date and sum of all planned cost at the (usually same) end date.
I have uploaded excel and PowerBi Files HERE .
I hope this helps to clarify my target.
Thanks,
David
Your question helped to solve the first case - which is the most important.
I created the following table, and when you select A1 (or any other single "Project Detail" selection with the slicer, it works like a charm:
| Project | Project Detail | Date | Cost | Category |
| A | A1 | 28.02.2021 00:00 | 200 | Actual |
| A | A1 | 28.05.2021 00:00 | 200 | Actual |
| A | A1 | 28.08.2021 00:00 | 400 | Actual |
| A | A1 | 28.10.2021 00:00 | 400 | Actual |
| A | A2 | 28.03.2021 00:00 | 500 | Actual |
| A | A2 | 28.05.2021 00:00 | 500 | Actual |
| A | A2 | 28.09.2021 00:00 | 800 | Actual |
| A | A2 | 28.12.2021 00:00 | 100 | Actual |
| B | B1 | 28.01.2021 00:00 | 100 | Actual |
| B | B1 | 28.02.2021 00:00 | 300 | Actual |
| B | B1 | 28.06.2021 00:00 | 300 | Actual |
| B | B1 | 28.08.2021 00:00 | 100 | Actual |
| B | B1a | 28.09.2021 00:00 | 400 | Actual |
| B | B1a | 28.12.2021 00:00 | 600 | Actual |
| B | B1a | 28.01.2022 00:00 | 200 | Actual |
| A | A1 | 28.01.2021 00:00 | 0 | Base |
| A | A2 | 28.02.2021 00:00 | 0 | Base |
| B | B1 | 28.12.2020 00:00 | 0 | Base |
| B | B1a | 28.08.2021 00:00 | 0 | Base |
| A | A1 | 27.12.2021 00:00 | 1000 | Base |
| A | A2 | 27.11.2021 00:00 | 2000 | Base |
| B | B1 | 25.02.2022 00:00 | 500 | Base |
| B | B1a | 25.02.2022 00:00 | 1500 | Base |
The pbix is still in the same location - HERE .
The result in the dashborad looks as expected.
However, if I want to combine two project details, as it makes sense for B1 and B1a, the result is not satisfying (see below), since I only want to see the first start date and the last end date, displayed as red manual line:
Is it possible to create the running total with e.g. an IF-condition for the "Base"-Category, so that only the first and last date values are displayed?
Again - thank you already for helping me with the first case. I would appreciate it a lot if you or another participant could help me with the second case.
Best regards,
David
Hi all
Hi @v-zhangti @lbendlin
I have uploaded excel and PowerBi Files HERE .
What I would like to achieve is to have a line graph with Actual Costs (Booking Date and Running Total for Actual COst) vs Ideal cost (Start/End Date, 0 Cost/Planned Cost).
It should then look like that, eg for Project A-A1:
I hope this helps to clarify my target.
Thanks,
David
Hi, @DavidDi
What kind of output do you expect in the end? For example, when Projekt selects A and Projektdetail selects A1, the X-axis of the line chart is the start time to the end time and the Y-axis is a straight line 1000?
Is this the output you expect? If not, please show the picture to show the result you expect.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I don't think you want a line graph.
see attached for details. One option is to generate data points between Start and Ende so that your chart gets filled. This option will result in too many data points if you go down to day level.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldRdCoMwDADgq0ifh6RJW92jCmN3EO+ya+x6O8mqpi7GnzoRodB+NH/2vWnMzTQ2fsCW8UXAcYFVaTEtiu75KDaP/bzeAGCGGyO4h9hzBDVCjLgFCSziIUIacYzcE0KQDcdpxMtwUObk+CZeI4ERn86RXOwjQSBt3NVaPvdLrC8hc5PxMtLAjZHP61jhFUIyGq4w0DmCGuHiWFiQOhsNacTLcFCmhE4rvEKCrkdsE6jyFU5IF3d10+ggdwb83/UTghukFgnKd/2E0IzMBbk4xE4jjpFajk4mHK8RL8NZ/Qmudf2EpNFx6RzkR6dKyPAF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Projekt = _t, Projektdetail = _t, Start = _t, Ende = _t, #"Geplante Kosten" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start", type date},{"Ende", type date},{"Geplante Kosten", Currency.Type}}, "de-CH"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Date", each List.Dates([Start],Number.From([Ende])-Number.From([Start])+1,#duration(1,0,0,0))),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
#"Changed Type"
Another option is to only generate data points for Start and Ende.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldRdCoMwDADgq0ifh6RJW92jCmN3EO+ya+x6O8mqpi7GnzoRodB+NH/2vWnMzTQ2fsCW8UXAcYFVaTEtiu75KDaP/bzeAGCGGyO4h9hzBDVCjLgFCSziIUIacYzcE0KQDcdpxMtwUObk+CZeI4ERn86RXOwjQSBt3NVaPvdLrC8hc5PxMtLAjZHP61jhFUIyGq4w0DmCGuHiWFiQOhsNacTLcFCmhE4rvEKCrkdsE6jyFU5IF3d10+ggdwb83/UTghukFgnKd/2E0IzMBbk4xE4jjpFajk4mHK8RL8NZ/Qmudf2EpNFx6RzkR6dKyPAF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Projekt = _t, Projektdetail = _t, Start = _t, Ende = _t, #"Geplante Kosten" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start", type date},{"Ende", type date},{"Geplante Kosten", Currency.Type}}, "de-CH"),
#"Renamed Columns" = Table.RenameColumns(Table.SelectColumns(#"Changed Type with Locale",{"Projekt", "Projektdetail", "Start", "Geplante Kosten"}),{{"Start", "Datum"}})
& Table.RenameColumns(Table.SelectColumns(#"Changed Type with Locale",{"Projekt", "Projektdetail", "Ende", "Geplante Kosten"}),{{"Ende", "Datum"}})
in
#"Renamed Columns"
But then you lose the ability to "stack" costs on top of each other. Not sure how important that is.
See PBIX attached.
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
