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.
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.
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 |
---|---|
39 | |
25 | |
20 | |
14 | |
8 |
User | Count |
---|---|
71 | |
47 | |
46 | |
20 | |
16 |