cancel
Showing results for
Did you mean:
Highlighted
New Member

## Forecast vs Actual Line Graph for current week or month

Hi Community!

I have set targets for calories consumed on a daily basis over the course of a week/month. I want to view this as a cumulative line graph against the actual amount of calories i have consumed (budget vs actual).

I have created 2 views on power bi using the following tables as data sources:

Table 1

 Day Date CalorieTarget CalorieAcutal Sunday 22/03/2020 2000 3000 Monday 23/03/2020 2000 2500 Tuesday 24/03/2020 2000 2064 Wednesday 25/03/2020 2000 2200 Thursday 26/03/2020 2000 2096 Friday 27/03/2020 2000 1939 Saturday 28/03/2020 2000 2005 Sunday 29/03/2020 2000 2295 Monday 30/03/2020 2000 2050 Tuesday 31/03/2020 2000 2282 Wednesday 01/04/2020 2000 2044 Thursday 02/04/2020 2000 2029 Friday 03/04/2020 2000 2112 Saturday 04/04/2020 2000 2193 Sunday 05/04/2020 2000 2500 Monday 06/04/2020 2000 3000

Line Graph A: which uses column C&D from table 1 above

Current View using Table 1

TABLE 2

 Day Date CalorieTarget CalorieActual Sunday 22/03/2020 2000 3000 Monday 23/03/2020 2000 2500 Tuesday 24/03/2020 4000 4564 Wednesday 25/03/2020 6000 6764 Thursday 26/03/2020 8000 8860 Friday 27/03/2020 10000 10799 Saturday 28/03/2020 12000 12804 Sunday 29/03/2020 14000 15099 Monday 30/03/2020 2000 2050 Tuesday 31/03/2020 4000 4332 Wednesday 01/04/2020 6000 6376 Thursday 02/04/2020 8000 8405 Friday 03/04/2020 10000 10517 Saturday 04/04/2020 12000 12710 Sunday 05/04/2020 14000 15210 Monday 06/04/2020 2000 3000

Line graph B which uses columns C&D from table 2 above

Current View using Table 2

DESIRED OUTCOME:

I want to have a view such as seen by the Line Graph B that draws data from Table 1, instead of Table 2 . How do i make Table 1 a cumulative line graph?

I also want to be able to view only the current weeks or months data, starting at only the first day of the week or month.

(if it is wednesday, i want to see my forecast calories from the last Monday through to sunday against the actual calories i have consumed so far (Monday-Wednesday) to give me a quick view to see if i am on track to meet my weekly goal.

Please feel free to point me in the direction of other answers.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Forecast vs Actual Line Graph for current week or month

HI @LiamReidy,

#1, You can use the following measure formulas to calculate rolling WTD actual or target based on date field:

``````WTD Acutal =
VAR currDate =
MAX ( Test[Date] )
RETURN
CALCULATE (
SUM ( Test[Calorie Acutal] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( currDate )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
&& [Date] <= currDate
)
)

WTD Target =
VAR currDate =
MAX ( Test[Date] )
RETURN
CALCULATE (
SUM ( Test[Calorie Target] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( currDate )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
&& [Date] <= currDate
)
)
``````

#2, I think you need to create a new table with date field which not has a relationship to current table records, then you can use it as the source field on slicer to interact new measures formulas:

``````Selected PWTD Target =
VAR currDate =
MAX ( Selector[Date] )
VAR prevWeekEnd =
currDate - WEEKDAY ( currDate, 2 )
RETURN
CALCULATE (
SUM ( Test[Calorie Target] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( prevWeekEnd )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( prevWeekEnd, 2 )
&& [Date] <= prevWeekEnd
)
)

Selected WTD Actual=
VAR currDate =
MAX ( Selector[Date] )
RETURN
CALCULATE (
SUM ( Test[Calorie Actual] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( currDate )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
&& [Date] <= currDate
)
)``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
3 REPLIES 3
Super User IV

## Re: Forecast vs Actual Line Graph for current week or month

@LiamReidy, In this file I have created WTD using rank and weekday. I think same should work for you in this cae

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

Does the above reply helps. if you need more help mrke me @

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Community Support

## Re: Forecast vs Actual Line Graph for current week or month

HI @LiamReidy,

#1, You can use the following measure formulas to calculate rolling WTD actual or target based on date field:

``````WTD Acutal =
VAR currDate =
MAX ( Test[Date] )
RETURN
CALCULATE (
SUM ( Test[Calorie Acutal] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( currDate )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
&& [Date] <= currDate
)
)

WTD Target =
VAR currDate =
MAX ( Test[Date] )
RETURN
CALCULATE (
SUM ( Test[Calorie Target] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( currDate )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
&& [Date] <= currDate
)
)
``````

#2, I think you need to create a new table with date field which not has a relationship to current table records, then you can use it as the source field on slicer to interact new measures formulas:

``````Selected PWTD Target =
VAR currDate =
MAX ( Selector[Date] )
VAR prevWeekEnd =
currDate - WEEKDAY ( currDate, 2 )
RETURN
CALCULATE (
SUM ( Test[Calorie Target] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( prevWeekEnd )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( prevWeekEnd, 2 )
&& [Date] <= prevWeekEnd
)
)

Selected WTD Actual=
VAR currDate =
MAX ( Selector[Date] )
RETURN
CALCULATE (
SUM ( Test[Calorie Actual] ),
FILTER (
ALLSELECTED ( Test ),
YEAR ( [Date] ) = YEAR ( currDate )
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
&& [Date] <= currDate
)
)``````

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
New Member

## Re: Forecast vs Actual Line Graph for current week or month

@amitchandak @v-shex-msft  Thank you both! i will try both your suggestions and feedback to you later on 🙂

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors