Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear Community,
I am strugling to understand and fix the issue of empty subtotals for the measures I created in the Power Pivot model.
Please see below the view of the Pivot table:
The YoY measure returns same week previous year sales value (eg. Actual Booking in week 2 of year 2023 ($623K) are reflected as YoY Actual Bookings for week 2 of year 2024).
YoY measure synthax:
YoY:=CALCULATE([Actual Bookings],
(FILTER(ALL(T_Calendar),
T_Calendar[Fiscal Year]=IF(HASONEVALUE(T_Calendar[Fiscal Year]), VALUES(T_Calendar[Fiscal Year]))-1&&
T_Calendar[Fiscal Week]=IF(HASONEVALUE(T_Calendar[Fiscal Week]), VALUES(T_Calendar[Fiscal Week])))))
Could you please advise why the measure is present in the row of the Pivot table for each week but not visible in Subtotal while other rows in Pivot table are there in Subtotal? What should I do to fix it?
Thank you in advance!
Thanks, wdx223_Daniel. Appreciate if you could suggest what I should change in formula to make it work correctly.
YoY:=CALCULATE([Actual Bookings],
(FILTER(ALL(T_Calendar),
T_Calendar[Fiscal Year]=IF(HASONEVALUE(T_Calendar[Fiscal Year]), VALUES(T_Calendar[Fiscal Year]))-1&&
T_Calendar[Fiscal Week] IN VALUES(T_Calendar[Fiscal Week]))))
in subtotal column, HASONEVALUE was not fullfilled, it will give a blank, ie false. so there is not dates included.
User | Count |
---|---|
58 | |
21 | |
19 | |
17 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |