Forum Discussion

Si_7777's avatar
Si_7777
Icon for Advocate II rankAdvocate II
1 year ago
Solved

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 ...
  • johnbasha33's avatar
    johnbasha33
    1 year ago

    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 !!




rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

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.