Forum Discussion

Franco's avatar
Franco
Frequent Visitor
3 years ago
Solved

Measure Alert if not between two values

Hello everybody,
i have the following problem.

 

The data model is as follows

 

I need to find a measure that indicates whether the Rolling Avg Sales 3 Months is out of range (fields from/to from table Models)

 

The Measure "Rolling Avg Sales 3 Months" is

Rolling Avg Sales 3 Months = 
VAR _lastDate = 
    LASTDATE(DimDate[Date])
RETURN
    CALCULATE(
        AVERAGEX(VALUES(DimDate[Month]), [Total Sales]),
        FILTER(
            ALL(DimDate),
            DimDate[Date] <= _lastDate &&
            DimDate[Date] >= DATEADD(_lastDate,-3,MONTH)
        )
    )

 

 

thanks for your attention
Franco

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Franco 
    Please use

    NewMeasure =
VAR From =
    CALCULATE (
        SELECTEDVALUE ( Medels[From] ),
        CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH )
    )
VAR To =
    CALCULATE (
        SELECTEDVALUE ( Medels[To] ),
        CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH )
    )
VAR RollingAvg = [Rolling Avg Sales 3 Months]
RETURN
    IF (
        NOT ISBLANK ( RollingAvg ),
        IF ( RollingAvg >= From && RollingAvg <= To, "IN RANGE", "OUT OF RANGE" )
    )

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Franco Maybe:

    Measure =
  VAR __From = MAX('Table'[From])
  VAR __To = MAX('Table'[To])
  VAR __RollingAvg = [Rolling Avg Sales 3 Months]
RETURN
  IF( __RollingAvg >= __From && __RollingAvg <= __To, "IN RANGE", "OUT OF RANGE")
    • Franco's avatar
      Franco
      Frequent Visitor
      3 years ago

      Thanks Greg_Deckler 

      but even with your measure, the records are repeated for each model.

       

      I only need the green lines....

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Franco 

    please try

    NewMeasure =
VAR From =
    CALCULATE (
        SELECTEDVALUE ( Medels[From] ),
        CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH )
    )
VAR To =
    CALCULATE (
        SELECTEDVALUE ( Medels[To] ),
        CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH )
    )
VAR RollingAvg = [Rolling Avg Sales 3 Months]
RETURN
    IF ( RollingAvg >= From && RollingAvg <= To, "IN RANGE", "OUT OF RANGE" )
    • Franco's avatar
      Franco
      Frequent Visitor
      3 years ago

      Hi tamerj1 ,

      thanks for the answer but if i put your measure in the table, each row is multiplied by each model

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi Franco 
        Please use

        NewMeasure =
VAR From =
    CALCULATE (
        SELECTEDVALUE ( Medels[From] ),
        CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH )
    )
VAR To =
    CALCULATE (
        SELECTEDVALUE ( Medels[To] ),
        CROSSFILTER ( Models[ModelID], Items[ModelID], BOTH )
    )
VAR RollingAvg = [Rolling Avg Sales 3 Months]
RETURN
    IF (
        NOT ISBLANK ( RollingAvg ),
        IF ( RollingAvg >= From && RollingAvg <= To, "IN RANGE", "OUT OF RANGE" )
    )