Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nogi
Advocate II
Advocate II

Calculating true variance

I have a logic in my head which I am trying to work out the best way to implement using Power BI. I want to show variance (in days) for tasks using the following rules:

  • Closed - Actual Finish - Baseline Finish
  • Current - Today - Baseline Finish
  • Future - Baseline Finish - Planned Finish

My datasource is from a sharepoint task list that is synced with Project Online so all those values are available, I am just not sure where to start with applying the above rules.

2 ACCEPTED SOLUTIONS

For the moment, I have a calculate filed in project that gives me the value required:

 

IIf([% Complete]<100,IIf([Finish]>now(),DateDiff("d",[Baseline Finish],[Finish]),DateDiff("d",[Baseline Finish],now())),DateDiff("d",[Actual Finish],[Baseline Finish]))

View solution in original post

nogi
Advocate II
Advocate II

I played around some more and have found it easier to do the same with DAX:

 

Column: CheckVariance = IF(ISBLANK('Tasks'[TaskActualFinishDate].[Date]),
	IF(TODAY()>'Tasks'[TaskFinishDate].[Date],TODAY()-'Tasks'[BaselineFinishDate].[Date],
	'Tasks'[TaskFinishDate].[Date] - 'Tasks'[BaselineFinishDate].[Date]),
	'Tasks'[TaskActualFinishDate].[Date] - 'Tasks'[BaselineFinishDate].[Date])

Now, what if I only wanted to know the business days and exclude weekends from the count?

View solution in original post

5 REPLIES 5
nogi
Advocate II
Advocate II

I played around some more and have found it easier to do the same with DAX:

 

Column: CheckVariance = IF(ISBLANK('Tasks'[TaskActualFinishDate].[Date]),
	IF(TODAY()>'Tasks'[TaskFinishDate].[Date],TODAY()-'Tasks'[BaselineFinishDate].[Date],
	'Tasks'[TaskFinishDate].[Date] - 'Tasks'[BaselineFinishDate].[Date]),
	'Tasks'[TaskActualFinishDate].[Date] - 'Tasks'[BaselineFinishDate].[Date])

Now, what if I only wanted to know the business days and exclude weekends from the count?

v-jiascu-msft
Employee
Employee

Hi @nogi,

 

1. You need to import all the data into Power BI Desktop;

2. It seems you want to group the data by rules. Then you can add a calculated column with "Closed", "Current" and "Future".

3. Use the build-in functions. Maybe it looks like this:

varPopulation =
VAR.P ( Sales[Quantity] )

The reports could be:

Rules      varPopulation

Closed    0.88

Current   1.66

Future     2.1

 

Calculating true variance.jpg

 

 

 

 

 

 

 

 

If you have more questions, please provide your .pbix file.

 

Best Regards!

Dale

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

hi @v-jiascu-msft,

 

No pbix file as of yet as I want to build it this weekend, just trying to plan it out first. thanks for the idea but I am actually wanting the variance in the one column rather than 3 based on those rules. I have a project online schedule which I will be importing into the desktop client to do this.

 

so, just to clarify, I will have tasks - some already completed, some in progress and some not started (future tasks). The logic should be something like:

if task = closed then
     variance = actual close - baseline close
else if task startdate <= today then
     variance = today - forecast date
else
     variance = forecast date - baseline date
endif

If closed, I want forumula 1 to be used, if open

For the moment, I have a calculate filed in project that gives me the value required:

 

IIf([% Complete]<100,IIf([Finish]>now(),DateDiff("d",[Baseline Finish],[Finish]),DateDiff("d",[Baseline Finish],now())),DateDiff("d",[Actual Finish],[Baseline Finish]))

I see now. I thought it was the variance in Statistics.

 

Best Regards!

Dale

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors