Dynamic Weekly total triggering failure KPI

[AlanP514]

Hi all, 
I have a requirement, Please help me to achieve this task
I have a requirement, I am working on the azure data, in one day many triggers will happen(Refreshing, adding new data)
so, by default, i filtered based on the last 2 weeks' data like this 

Task is. i want to create one KPI Card to find out weekly how many trigger failures happened 

Based on the 0 values we can find how many triggers is failed during the last 2 weeks
Can anyone please help me to find out this solution?

[memepu · Accepted solution]

Hi @AlanP514 ,

I have created a simple sample, please refer to it to see if it helps you.

Dynamic Weekly total triggering failure KPI.pbix

Measure =
VAR _this =
    WEEKNUM ( TODAY (), 2 )
VAR _before2week = _this - 2
VAR _count =
    CALCULATE (
        COUNT ( Sheet1[Bronze] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[Bronze] = 0
                && Sheet1[weeknum] >= _before2week
                && Sheet1[weeknum] <= _this
        )
    )
RETURN
    IF (
        MAX ( Sheet1[weeknum] ) >= _before2week
            && MAX ( Sheet1[weeknum] ) <= _this,
        _count,
        BLANK ()
    )

If I have misunderstood your meaning, please provide more details with your desired output.

Best regards.

[amitchandak]

@AlanP514 , With help from separate week/date table and week rank, you find that

example

the new column needs in date/week table

measures

Last 2 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-2 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

or

Last 2 weeks =
var _max1 = maxx(allselected('Table'), 'Table'[date])
var _max = maxx(filter(all(date), date[date] = _max1), week[Rank])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-2 && 'Date'[Week Rank]<=_max))

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format