How to differentiate between target and actual per month using only one dataset

[Benjamin4312]

Hello guys

We want to track project savings with Microsoft Lists in Sharepoint Online. I created the lists as follows: One Line of data for planned and actual and the savings are distributed over a period ot 12 month. This would work perfectly fine, but the one thing thats a bit annoying is that you have to type in your project master data twice, once for planned and once for actual. This doesnt seems that user friendly and is risky and prone for errors.

One Idea was to create colums as Jan 19 planned - Jan 19 actual, but I guess then i will have the issue, that Power BI dont recongnise the colums as a  "date field" which I need at the end for calcluating the YTD etc.

Does anyone have an idea how to solve this so that the user only enters data once, but Power BI can easily distinguish between actual and planned data?

P.S. acutall main issue is, that the Lists app has huge performance issue when try to editing the data, so we want the date enter to reduce to once.

[v-jialongy-msft · Accepted solution]

Hi @Benjamin4312 

I can give you some further suggestions for your idea.Once your data is in Power BI, you can create calculated columns or measures to differentiate between planned and actual values and perform your YTD calculations or any other analysis. 

For example ”3/17/2024 - 5/17/2024“.You can use the following DAX to break them apart:

actual = LEFT('Table'[Column1],10)

planned = RIGHT('Table'[Column1],10)

 This is the result:

Then you just need to change the format of the new column to date format.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.