Forum Discussion
DAX Help count weeks over forecast
Wonder if anyone can help.
I made a graph of sales vs fc vs stretch (stretch being above fc). I have written a measure to colour code bars according to over fc or over stretch value. I thought it be nice to have a card stating X week count over FC, how would I achieve this?
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 !!
4 Replies
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.
Hi Si_7777
Yes, you can create a DAX measure to count the number of weeks where actual sales exceeded forecast values, and display it in a card visual. Assuming your dataset has a Week column and columns for Actual sales and Forecast Sales, you can use the following measure:
Weeks Over Forecast = CALCULATE(COUNTROWS(VALUES('SalesData'[Week])), FILTER(VALUES('SalesData'[Week]), CALCULATE(SUM('SalesData'[Actual Sales])) > CALCULATE(SUM('SalesData'[Forecast Sales]))))This measure works by evaluating each unique week and counting only those where the total actual sales are greater than forecasted sales. It handles filter context correctly, works with slicers, and avoids issues caused by repeated weeks or row-by-row comparison making it a reliable solution for weekly-level reporting.
Hi Si_7777,
Have you had a chance to review the solution we shared by rohit1991 johnbasha33 ? If the issue persists, feel free to reply so we can help further.
Thank you.
