Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Alternative DAX Formula

Hi Everyone, Currently I am using one formula to find the closed deals based on date. I have created a stacked bar chart which can give the closed deals count based on date. I have used this f...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks for your hep and it resolved now. I used different aproach to solve it

    CALCULATE (
    DISTINCTCOUNT (pred[deal] ),
    pred[ACTUAL_STATUS] <> "Open",keepfilters(filter(pred, pred[MAX_CLOSED_DATE]=pred[PREDICTION_DATE])))
johnt75's avatar
johnt75
Super User
4 years ago

You could try

Closed deals =
VAR DealAndDate =
    ADDCOLUMNS (
        VALUES ( pred[deal] ),
        "@date", CALCULATE ( MAX ( pred[PREDICTION_DATE] ) )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( pred[deal] ),
        pred[ACTUAL_STATUS(deal_status)] <> "Open",
        DealAndDate
    )
RETURN
    Result
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for your response, it is giving the total count correctly. But when you plot the visual ( stacked bar: date: X-axis, Cout- Y-axis)it is not giving the exact count of deals which got closed on perticular date.
It is showing as summation for previous days count

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    is it possible to share a PBIX with any confidential information removed ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sorry to say, It's highly confidential data

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        what relationships do you have from your date table to the predictions table ? on which columns and are they one-to-many or many-to-many? in which directions do the filters flow ?