Card to return recent value/blank and go in history

[Nishlija995]

Hello,

I want to track KPI every working day, KPI is always signed for a day before, also if user didn't provide data it will show (blank) which is also good. What I want to achieve is to also be able to set filter and go in history in each week/month/quaral, but I am unable to do that with this code, it is always returning (blank), probably because I filter it by "today()" but I'm not sure what to modify to achieve this.

My code so far for card1 (single line each day)

card1 = 
If(WEEKDAY(today()) = 2,
CALCULATE (
    AVERAGE( db_signedData[Actual] ),
    Filter(db_signedData, db_signedData[KPI] = "foo" && db_signedData[created_date].[Date] = today()-3)),
CALCULATE (
    AVERAGE( db_signedData[Actual] ),
    Filter(db_signedData, db_signedData[KPI] = "foo" && db_signedData[created_date].[Date] = today()-1)))

Code so far for card2 (sum for whole week):

card2 = 
If(WEEKDAY(today()) = 2,
CALCULATE (
    sum( db_signedData[Actual] ),
    Filter(db_signedData, db_signedData[KPI] = "foo" && db_signedData[cw] = WEEKNUM(today())-1)),
CALCULATE (
    sum( db_signedData[Actual] ),
    Filter(db_signedData, db_signedData[KPI] = "foo" && db_signedData[cw] = WEEKNUM(today()))))

[v-shex-msft]

Hi @Nishlija995,

The filter effect will apply to the visual to filter records at first with high priority from the table relationship mappings.

If you want to use slicer to achieve selector effects, I’d like to suggest you break the current date filter effect and add ALLSELECTED function to your expression to help records correctly filter table ranges.

card1 =
IF (
    WEEKDAY ( TODAY () ) = 2,
    CALCULATE (
        AVERAGE ( db_signedData[Actual] ),
        FILTER (
            ALLSELECTED ( db_signedData ),
            db_signedData[KPI] = "foo"
                && db_signedData[created_date]
                    = TODAY () - 3
        )
    ),
    CALCULATE (
        AVERAGE ( db_signedData[Actual] ),
        FILTER (
            ALLSELECTED ( db_signedData ),
            db_signedData[KPI] = "foo"
                && db_signedData[created_date]
                    = TODAY () - 1
        )
    )
)

Regards,

Xiaoxin Sheng

[Nishlija995]

Hello @v-shex-msft ,

Thank you for reply.

I'm not sure that this is right code for me, maybe I didn't explained well.

One way is to have kinda "dynamic" slicer, which will if todays is monday return records for last week, and if today is any other day to return current week. Also, to have fuction to return any other week choosen in slicer (average/sum for selected week).

[v-shex-msft · Accepted solution]

HI @Nishlija995,

It sounds like you want to use slicer to achieve selector effect instead of the filter.

If that is the case, I'd like to suggest you creating a unconned tale as source slicer, then you can extract the selection to interaction with formula calculation ranges:

card1 =
VAR selection =
    MAX ( Table[Date] )
RETURN
    IF (
        WEEKDAY ( selection ) = 2,
        CALCULATE (
            AVERAGE ( db_signedData[Actual] ),
            FILTER (
                ALLSELECTED ( db_signedData ),
                db_signedData[KPI] = "foo"
                    && db_signedData[created_date] = selection - 3
            )
        ),
        CALCULATE (
            AVERAGE ( db_signedData[Actual] ),
            FILTER (
                ALLSELECTED ( db_signedData ),
                db_signedData[KPI] = "foo"
                    && db_signedData[created_date] = selection - 1
            )
        )
    )

card2 =
VAR selection =
    MAX ( Table[Date] )
RETURN
    IF (
        WEEKDAY ( selection ) = 2,
        CALCULATE (
            SUM ( db_signedData[Actual] ),
            FILTER (
                ALLSELECTED ( db_signedData ),
                db_signedData[KPI] = "foo"
                    && db_signedData[cw]
                        = WEEKNUM ( selection ) - 1
            )
        ),
        CALCULATE (
            SUM ( db_signedData[Actual] ),
            FILTER (
                ALLSELECTED ( db_signedData ),
                db_signedData[KPI] = "foo"
                    && db_signedData[cw] = WEEKNUM ( selection )
            )
        )
    )

Regards,

Xiaoxin Sheng