Forum Discussion

nogi's avatar
nogi
Advocate II
8 years ago
Solved

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.

Need Help
  • nogi's avatar
    nogi
    8 years ago

    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]))
  • nogi's avatar
    nogi
    8 years ago

    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?

5 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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

     

     

     

     

     

     

     

     

     

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

     

    Best Regards!

    Dale

    • nogi's avatar
      nogi
      Advocate II
      8 years ago

      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

      • nogi's avatar
        nogi
        Advocate II
        8 years ago

        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]))
  • nogi's avatar
    nogi
    Advocate II
    8 years ago

    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?