Line graph comparing a year's forecast with YTD sales.

[Ramps]

WEEKS is a calendar file.

TRACKERFILE contains a list of products, the week number, the weekly forecast and the weekly sales.

The forecast is pre-populated for weeks 1 – 52 because it is a forecast, but the sales are only populated up to the current week 13 because weeks 14 – 52 have not happened yet.

I want to display a line graph showing sales against forecast.

The forecast graph line (in yellow) must show all 52 weeks.

The sales graph line (in red) must just show up to the current week 13.

I have got the forecast graph line working ok with
YTD Forecast = TOTALYTD (SUM (Trackerfile[Forecast]), 'Weeks'[End of Week])
but the sales is flat lining from week 14 to 52 instead of stopping at week 13.

How I can achieve the desired graph please which compares the year’s forecast with the YTD sales?

This PBIX example will be available until 1May2018

https://modusuk-my.sharepoint.com/:u:/g/personal/mike_ramplin_onepay_co_uk/EV4phqFbLWFNvdfxgg-C5WYBi...

TRACKER FILE

Week	Product	Forecast	Sales
1	TVs	17	5
2	TVs	47	60
3	TVs	54	56
4	TVs	66	12
5	TVs	32	7
6	TVs	53	95
7	TVs	13	43
8	TVs	35	24
9	TVs	6	85
10	TVs	3	6
11	TVs	59	21
12	TVs	13	37
13	TVs	19	46
14	TVs	31	0
15	TVs	16	0
16	TVs	92	0
17	TVs	44	0
18	TVs	78	0
19	TVs	39	0
20	TVs	11	0
21	TVs	16	0
22	TVs	30	0
23	TVs	2	0
24	TVs	42	0
25	TVs	34	0
26	TVs	80	0
27	TVs	65	0
28	TVs	76	0
29	TVs	11	0
30	TVs	71	0
31	TVs	63	0
32	TVs	43	0
33	TVs	16	0
34	TVs	0	0
35	TVs	100	0
36	TVs	50	0
37	TVs	36	0
38	TVs	93	0
39	TVs	88	0
40	TVs	51	0
41	TVs	62	0
42	TVs	81	0
43	TVs	71	0
44	TVs	29	0
45	TVs	2	0
46	TVs	39	0
47	TVs	43	0
48	TVs	9	0
49	TVs	9	0
50	TVs	47	0
51	TVs	76	0
52	TVs	65	0

WEEKS

Week	End of Week	Last week refreshed	YTD
1	06/01/2018	14	Y
2	13/01/2018	14	Y
3	20/01/2018	14	Y
4	27/01/2018	14	Y
5	03/02/2018	14	Y
6	10/02/2018	14	Y
7	17/02/2018	14	Y
8	24/02/2018	14	Y
9	03/03/2018	14	Y
10	10/03/2018	14	Y
11	17/03/2018	14	Y
12	24/03/2018	14	Y
13	31/03/2018	14	Y
14	07/04/2018	14	N
15	14/04/2018	14	N
etc	etc	etc	etc
51	22/12/2018	14	N
52	29/12/2018	14	N

[OwenAuger · Accepted solution]

Hi @Ramps

Rewrite your YTD Sales measure so that it checks if the max Week currently selected is less than or equal to the maximum week with nonzero Sales in the entire TRACKERFILE table. This is one way of doing it:

YTD Sales =
VAR MaxSalesWeek =
    CALCULATE (
        MAX ( TRACKERFILE[Week] ),
        ALL ( TRACKERFILE ),
        TRACKERFILE[Sales] > 0
    )
RETURN
    IF (
        MAX ( WEEKS[Week] ) <= MaxSalesWeek,
        TOTALYTD ( SUM ( TRACKERFILE[Sales] ), 'WEEKS'[End of Week] )
    )

As a side note, I would recommend considering a complete Date table with contiguous dates. This is recommended when using time intelligence functions, and if you did include more than a year of data in your model it would be necessary.

Regards,

Owen

[OwenAuger · Accepted solution]

Hi @Ramps

Rewrite your YTD Sales measure so that it checks if the max Week currently selected is less than or equal to the maximum week with nonzero Sales in the entire TRACKERFILE table. This is one way of doing it:

YTD Sales =
VAR MaxSalesWeek =
    CALCULATE (
        MAX ( TRACKERFILE[Week] ),
        ALL ( TRACKERFILE ),
        TRACKERFILE[Sales] > 0
    )
RETURN
    IF (
        MAX ( WEEKS[Week] ) <= MaxSalesWeek,
        TOTALYTD ( SUM ( TRACKERFILE[Sales] ), 'WEEKS'[End of Week] )
    )

As a side note, I would recommend considering a complete Date table with contiguous dates. This is recommended when using time intelligence functions, and if you did include more than a year of data in your model it would be necessary.

Regards,

Owen

[Ramps]

Thank you Owen.  See screen print below.

That forumula drew the graph exactly how I wanted, but I did not how to do it.

Where did you learn please?  I am looking for training or sources of knowledge

I willl take your side note on board, have you any examples of complete date tables with contiguous dates. I have not seens any examples that fullfill my purposes in the forums.

[Ramps]

I have spotted a  problem with your solution Owen.

If weeks have no sales then they wont show on the graph.

Is is possible to change the formula to use the YTD flag in the WEEKS file rather than the check if the max week currently selected is less than or equal to the maximum week with nonzero Sales?

Sorry I dont know how to do this.

[OwenAuger · Accepted solution]

Hi again,

I missed that you have a YTD column. Hmm, using that column, you can rewrite the measure like this:

YTD Sales = 
IF (
    CALCULATE ( SELECTEDVALUE ( WEEKS[YTD] ), FIRSTDATE ( WEEKS[End of Week] ) )
        = "Y",
    TOTALYTD ( SUM ( TRACKERFILE[Sales] ), WEEKS[End of Week] )
)

This ensures that a YTD Sales value is displayed as long as the earliest week filtered (or the single week if only one week filtered) has YTD = "Y".

On your other questions, there are various good books around - I have learnt from some of these:

• https://exceleratorbi.com.au/learn-to-write-dax/
• https://powerpivotpro.com/the-book/
• https://www.sqlbi.com/books/the-definitive-guide-to-dax/

Also read the blog posts on the same pages.

Also some videos on Microsoft's website on various Power BI topics:

https://docs.microsoft.com/en-us/power-bi/guided-learning/

The requirement for a date table with contiguous dates is discussed here:

https://www.daxpatterns.com/time-patterns/

Also have a look at this for a PBIT with a date table:

https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

Best regards,

Owen

[Ramps]

It will take a while for me to get through all that Owen, but I just wanted to say thank you straight away. 

Thank you.