Add Cumulative Variance to measure only for this quarter

[Matthias]

Hi,

I have a dataset with three tables:

- TasktimephasedCosts which has a projectid, taskid, timebyday and PlannedCosts

- Timeset with TimebyDay

- Actuals with TimebyDay, ProjectID, ActualCost

The goal is to present a matrix by Year, Quarter, Projects which shows the Actuals for quarters in the past, PlannedCosts for quarters in the future. the current quarter should show PlannedCosts + (Cumulative Variance of Planned Costs in Past quarters - Cumulative variance of Actuals).

I know it is not an ideal thing to do, but I have no other choice.

I have played around with calculated columns and measures, but I can't get it to work.

Any help would be appreciated.

[v-yulgu-msft]

Hi @Matthias,

Please share sample data and your deaired output to help us better understand your scenario. You could follow this blog to provide necessary information.

Regards,

Yuliana Gu

[Matthias · Accepted solution]

Sure,

1. I first calculated the Cumulative Actuals and Cumulative Taskcosts in the past separately.

2. Another measure to calculate the variance between the two.

3. I then added two calculated columns in the table 'Timeset' :

  - IsBeforeThisQuarter = checks if date is before this quarter

  - Istoday = 1 if date is Today

4. My result (Rolling Forecast) was calculated in three parts:

  - The sum of Actuals for which the related column IsBeforeThisQuarter = True

  - The sum of Taskcosts for which the related column IsBeforeThisQuarter = False

  - Sum of IsToday (which is 1 in the current Quarter) * (The difference in previous Cumulative Actuals and Cumulative Taskcosts)

I'm pretty sure that this is not the most efficient way, but it does the trick.

Happy to hear suggestions to improve on the solution.

Code below:

CumulativeActualPrevious = calculate(sum(Actuals[ActualsinEUR]);filter(All(TimeSet[TimeByDay]);TimeSet[TimeByDay]<MIN(TimeSet[TimeByDay])))

CumulativeTaskCostsPrevious = calculate(sum(TaskTimephasedDataSet[TaskCostinEUR]);filter(All(TimeSet[TimeByDay]);TimeSet[TimeByDay]<MIN(TimeSet[TimeByDay])))

Variance Rolling Forecast = [CumulativeTaskCostsPrevious]-[CumulativeActualPrevious]

IsToday = if(TimeSet[TimeByDay].[Date]=Today();1;0)

CountToday = sum(TimeSet[IsToday])

IsBeforeThisQuarter = OR(TimeSet[Year]<[CurrentYear];AND(TimeSet[Year]=[CurrentYear];TimeSet[TimeQuarter]<[CurrentQuarter]))


RollingForecast = Calculate(SUM(Actuals[ActualsinEUR]);Actuals[IsBeforeThisQuarter]=True)+Calculate(sum(TaskTimephasedDataSet[TaskCostinEUR]);TaskTimephasedDataSet[IsBeforeThisQuarter]=FALSE())+[CountToday]*[Variance Rolling Forecast]

[Matthias]

Hi,

I will do it next time. For the moment I found a solution that works.

Do I close this question?

[v-yulgu-msft]

Hi @Matthias,

Glad to hear that you have resolved it by yourself. Would you please share your solution with us and accept it as an answer so that it can benefit more users?

Regards,

Yuliana Gu