DAX - KPI number of days under a specific value

[HansB]

I have one date table and another table with values. It's build like this: Date, Variable 1, Variable 2. There can be multiple entries of a  date.

Now I have a KPI that's basically var 1/var 2. What I would like to achieve, is to display the days where the KPI was lower than a specific value (let's say: 70). 

So I thought this would be able with a DAX measure, but I failed to create one. 

I tried something like this:

KPI_daysbelowvalue = CALCULATE(COUNT(DimDate[DateId].[Day]);(SUM('Table'[Variable1])/SUM('Table'[Variable2])<70)))

Does anyone has an idea on how to solve this problem?

[v-yulgu-msft · Accepted solution]

Hi @HansB,

Please try this measure:

KPI_daysbelowvalue=
CALCULATE (
    COUNT ( DimDate[Date] ),
    FILTER (
        ALL ( 'Table' ),
        (
            CALCULATE (
                SUM ( 'Table'[Variable1] ),
                ALLEXCEPT ( 'Table', 'Table'[Date] )
            )
                / CALCULATE (
                    SUM ( 'Table'[Variable2] ),
                    ALLEXCEPT ( 'Table', 'Table'[Date] )
                )
        )
            < 70
    )
)

Best regards,

Yuliana Gu

[TomMartens]

Hey,

I guess you have relationship between your tables DimDate and Table, and I also assume that your DimTable is on the 1-side of the relationship.

To count the dates where your condition is TRUE you have to tackle the issue  by slightliy different approach.

Create a measure like so

KPI_daysbelowvalue = 
CALCULATE(
SUMX(
'table''
,IF('Table'[Variable1] / 'Table'[Variable2] < 70 ,1,BLANK())
))

Hopefully this gets you started

Regards

Tom

[HansB]

Thanks! Yes, the relation between both tables is a 1-m relationship where DimTable is on the 1-side. Youre measure is not working yet, since it somehow doesn't take the days into account. I'll try some other things tomorrow.