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.
Thanks for looking!
I'm not getting any totals using a Calendar filter for YOY data. The populated row data is correct, it's just not totaling.
NOTE: I haven't added a YOY column yet; this is just to get the previous data for that calculation.
I appreciate any insight.
Sales_Units =
CALCULATE (
SUMX ( Sales_Receipts, Sales_Receipts[Unit_Sales] )
)
Sales_Units_1Y =
VAR CurrentYear = SELECTEDVALUE ( F_Calendar_Month[F_Year] )
VAR CurrentMonth = SELECTEDVALUE ( F_Calendar_Month[F_Month_Nbr] )
RETURN
CALCULATE (
SUMX ( Sales_Receipts, Sales_Receipts[Unit_Sales] ),
FILTER (
ALL ( F_Calendar_Month ),
F_Calendar_Month[F_Month_Nbr] = CurrentMonth &&
F_Calendar_Month[F_Year] = CurrentYear - 1
)
)
Solved! Go to Solution.
Sales_Units = SUM( Sales_Receipts[Unit_Sales] )
// This code is brittle because such transformations
// should use the id's of months, not separate fields
// like year and month number. But you have not shown your
// model, so I have to assume the minimum, meaning
// you don't have such a field in your date table.
Sales_Units_1Y =
SUMX(
SUMMARIZE(
F_Calendar_Month,
F_Calendar_Month[F_Year],
F_Calendar_Month[F_Month_Nbr]
),
var CurrentYear = F_Calendar_Month[F_Year]
var CurrentMonth = F_Calendar_Month[F_Month_Nbr]
var Result =
CALCULATE(
[Sales_Units],
F_Calendar_Month[F_Year] = CurrentYear - 1,
F_Calendar_Month[F_Year] = CurrentMonth,
REMOVEFILTERS( F_Calendar_Month )
)
return
Result
)
Sales_Units = SUM( Sales_Receipts[Unit_Sales] )
// This code is brittle because such transformations
// should use the id's of months, not separate fields
// like year and month number. But you have not shown your
// model, so I have to assume the minimum, meaning
// you don't have such a field in your date table.
Sales_Units_1Y =
SUMX(
SUMMARIZE(
F_Calendar_Month,
F_Calendar_Month[F_Year],
F_Calendar_Month[F_Month_Nbr]
),
var CurrentYear = F_Calendar_Month[F_Year]
var CurrentMonth = F_Calendar_Month[F_Month_Nbr]
var Result =
CALCULATE(
[Sales_Units],
F_Calendar_Month[F_Year] = CurrentYear - 1,
F_Calendar_Month[F_Year] = CurrentMonth,
REMOVEFILTERS( F_Calendar_Month )
)
return
Result
)
I thought I posted a portion of the fiscal calendar table. Sorry. It looks like the attached. I have a field called "F_YM" which I would consider the ID.
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 |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |