Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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 |
Solved! Go to 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
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:
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
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
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.
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.
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:
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |