cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## Reducing rolling (cumulative) cost by percentage points

Hey @xifeng_L
Sorry for pinging you, but i figured it would probably be the easiest for you to get my issue since we worked on my case yesterday.
I also decided to open a new thread, so i can upvote and mark as accepted again, in case you manage to help me again 🙂

Demo - Rolling sum of date ranges (3).pbix

I would need another S-curve (line chart), that would be a % offset of the rolling cost (cumulative project cost) that you calculated with DAX formula. Every week (friday) we check project progress and note the % that the project is behind or infront of schedule.

If it's behind, i'd take the rolling cost value of that day and multiply it by the % that is behind.

So in this sample data, we have 3 data points where we measure project progress. We see that on January 5th we measured that the project is 20% behind schedule, meaning i would need to reduce the value of the rolling cost for that day by 20%. In this example i'd reduce 180 by 20% meaning the result should be 144.

Does that make sense?

1 ACCEPTED SOLUTION
Solution Sage

This can be achieved by adding conditions to the IF function at the end of the measure expression.

Such as:

3 REPLIES 3
Solution Sage

Hi @MatevzP ,

Just in case I misunderstood your needs, wrote two different logics for you, see which one meets your needs.

Demo - Rolling sum of date ranges(4).pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

Helper I

Hi, thank you for your response, as always 🙂
That works partially. If possible, i would like to remove other datapoints and only show data points from the "Date of Recording" column. Your formula takes date values from Calendar[Date] column, meaning every day. But i would like to show a line chart only from those 3 data points (or how ever many will be) from the "Project Progress"[Date of Recording] column.
We will have a project progress review every Friday. Meaning in the end of the year we will have 54 data points (54 calendar weeks). Every Friday we will asses the status of the project. If it's behind the progress for let's say 20%, then we would take that day's predicted project cost from rolling or cumulative formula that we have working and reduce it by said percentage. The rest of the days in between, we don't know that status of the progress so we don't want to show that data.

So your algorithm is calculating it correctly, i'd only like to hide data points for which we don't have "date of recording" data. Does that make sense?

Solution Sage

This can be achieved by adding conditions to the IF function at the end of the measure expression.

Such as:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors