Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
If a data source contains:-
How can a table report be produce with YTD qty by product by week number.
Note the date source may not have entries for every week, for example Phones were sold in week 1,2 & 4 but not 3,5 & 6. However, the report requires YTD qtys for each product each week.
I assume a week table, a relationship and a DAX measure or column could be used or convert the week into a date and use the date table. Examples of both methods would be helpful.
I have programmed in other languages but new to DAX. I looked at other posts on the forum and found them confusing becauseI I have not got to grasp with DAX sytntax yet, please can anyone provide a clear examples for a DAX beginner. Thank you.
SOURCE DATA | |||
Product | Week | Weekly Qty | |
Phone | 1 | 25 | |
Phone | 2 | 17 | |
Phone | 4 | 8 | |
Radio | 1 | 31 | |
Radio | 4 | 34 | |
Radio | 6 | 19 | |
TV | 2 | 14 | |
TV | 4 | 28 |
|
DESIRED REPORT | |||
Product | Week | Weekly Qty | YTD Qty |
Phone | 1 | 25 | 25 |
Phone | 2 | 17 | 42 |
Phone | 3 | 42 | |
Phone | 4 | 8 | 50 |
Phone | 5 | 50 | |
Phone | 6 | 50 | |
Radio | 1 | 31 | 31 |
Radio | 2 | 31 | |
Radio | 3 | 31 | |
Radio | 4 | 34 | 65 |
Radio | 5 | 65 | |
Radio | 6 | 19 | 84 |
TV | 1 | 0 | |
TV | 2 | 14 | 14 |
TV | 3 | 14 | |
TV | 4 | 28 | 42 |
TV | 5 | 42 | |
TV | 6 | 42 |
Hi @Ramps,
Please check out the demo here.
1. A week table full of weeks.
Weeks = GENERATESERIES(1, 52, 1)
2. A product table full of products.
Products = VALUES('SOURCE DATA'[Product])
3. Establish relationships.
4. Create a measure.
YTW = VAR ytw = CALCULATE ( SUM ( 'SOURCE DATA'[Weekly Qty] ), FILTER ( ALL ( Weeks ), 'Weeks'[week] <= MIN ( 'Weeks'[week] ) ) ) RETURN IF ( ISBLANK ( ytw ), 0, ytw )
5. Create a visual and enable "Show data without values".
Best Regards,
Dale
Many thanks Dale of going to the effort to creating a PBIX example
It is really helpful and I will use it. Thank you.
This formula is great for viewing in tables but I am still having problems with the line graphs.
See my post about "Line graph comparing a year's forecast with YTD sales."
Many thanks
RunningTotal = VAR MyWeek = MAX(WeeklyTotals[Week]) VAR MySum = CALCULATE(SUM([Weekly Qty]),FILTER(ALLEXCEPT(WeeklyTotals,WeeklyTotals[Product]),[Week]<=MyWeek)) RETURN MySum
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |