Forum Discussion
DAX Help count weeks over forecast
Hi Si_7777
ou can create a DAX measure to count the number of weeks where actual sales exceeded forecast (FC), and display that in a Card visual.
Option A: If your dataset is at week level
Weeks Over FC =
COUNTROWS(
FILTER(
VALUES(Sales[Week]),
CALCULATE(SUM(Sales[Actual])) > CALCULATE(SUM(Sales[Forecast]))
)
)
Option B: If your dataset is at daily level, group by week:
Weeks Over FC =
COUNTROWS(
FILTER(
SUMMARIZE('Date', 'Date'[Week Start], "ActualSales", CALCULATE(SUM(Sales[Actual])), "ForecastSales", CALCULATE(SUM(Sales[Forecast]))),
[ActualSales] > [ForecastSales]
)
)Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Hi Si_7777
ou can create a DAX measure to count the number of weeks where actual sales exceeded forecast (FC), and display that in a Card visual.
Option A: If your dataset is at week level
Weeks Over FC =
COUNTROWS(
FILTER(
VALUES(Sales[Week]),
CALCULATE(SUM(Sales[Actual])) > CALCULATE(SUM(Sales[Forecast]))
)
)
Option B: If your dataset is at daily level, group by week:
Weeks Over FC =
COUNTROWS(
FILTER(
SUMMARIZE('Date', 'Date'[Week Start], "ActualSales", CALCULATE(SUM(Sales[Actual])), "ForecastSales", CALCULATE(SUM(Sales[Forecast]))),
[ActualSales] > [ForecastSales]
)
)
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thank you, I wasnt using summerize. I have date table by day, sales fact, and forecast fact all joining by each day date. Option B worked for me.
Is there a way to see the result that summerize would produce (The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.) Just to aid my understanding of that function.