Forum Discussion

daveauk's avatar
daveauk
Advocate I
9 years ago

hide months without data based on calculation

I'm calculating month over month change.

 

 

 

Visual hides months without data when only using the numerator and demoninator:

 

60d+Chg =

CALCULATE(ARAgingBucketsAnes[60day+bucket])

/

CALCULATE(ARAgingBucketsAnes[60day+bucket],PREVIOUSMONTH(DateTable[DateKey]))

 

 

 

 

 

 

It includes months not in the data set but in the date table when subtracting by 1 to indicate the change:

 

60d+Chg =

CALCULATE(ARAgingBucketsAnes[60day+bucket])

/

CALCULATE(ARAgingBucketsAnes[60day+bucket],PREVIOUSMONTH(DateTable[DateKey]))

-1

 

 

 

 

 

 

 

Any help to remove the columns for months without data after subraction would be helpful.

 

Thanks.

 

DA

1 Reply

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    daveauk

     

    Please try to update the 60d+Chg measure as below. If it doesn’t work, could you please provide your table sample and the measure of 60day+bucket?

     

    60d+Chg = 
VAR Temp =
    CALCULATE ( ARAgingBucketsAnes[60day+bucket] )
        / CALCULATE (
            ARAgingBucketsAnes[60day+bucket],
            PREVIOUSMONTH ( DateTable[DateKey] )
        )
RETURN
    ( IF ( Temp <> BLANK(), Temp - 1 ) )

    Best Regards,

    Herbert