Forecast vs Actual Line Graph for current week or month

[LiamReidy]

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	Calorie Target	Calorie Acutal
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	Calorie Target	Calorie Actual
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!

[LiamReidy]

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

[v-shex-msft · Accepted solution]

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

[amitchandak]

@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 @

Appreciate your Kudos.