Value 7 days earlier than selected

[Zbig62]

Hello everyone

Please help

I want to get information about the number of measurements (count of result) 7 days earlier than the selected day

The table with sample data looks like this:

For example, I select the day: 2024-02-01 where there were 16 measurements and I need to create a measure showing how many measurements there were 7 days earlier

The measure I wrote gives a "Blank" result every time. Where is the error?

DAY - 7 =

var a = SELECTEDVALUE('public Measurement'[DAY])

RETURN

CALCULATE(

COUNT('public Measurement'[result]); FILTER('public Measurement'; 'public  Measurement'[DAY] = a - 7

              )

        )

[SachinNandanwar · Accepted solution]

You have to change the filter in your expression

DAY - 7 =

CALCULATE (
    COUNT ( 'public Measurement'[result] ),
    FILTER (
        'public Measurement',
        'public  Measurement'[DAY]
            = DATEADD ( SELECTEDVALUE ( 'public Measurement'[DAY] ), -7, DAY )
    )
)

[Zbig62 · Accepted solution]

I have a problem with the type of parameters in and I don't know how to deal with it

[v-xianjtan-msft · Accepted solution]

Hi @Zbig62 

I don't think you should use the “DAY” field in the "public Measurement" table as a slicer, please create a calendar table to use as a slicer. And don't create a relationship between the calendar table and the "public Measurement" table.

1. Create a calendar table and use it as a slicer.

Calendar = DISTINCT('public Measurement'[DAY])

 2. Replace the field “DAY” in your measure with the field “DAY” of calendar table.

DAY - 7 = 
CALCULATE(
    COUNT('public Measurement'[result]),
    FILTER(
        'public Measurement',
        'public Measurement'[DAY] = SELECTEDVALUE('Calendar'[DAY]) - 7
    )
)

Here is my simple test result:

By the way, when the day DAY-7 does not exist for the "DAY" in your data, the measure will return blank. For example, if you slicer selects 2024-02-01, DAY-7 is 2024-1-25, because the data for 2024-1-25 does not exist in your table, the measure returns blank.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xianjtan-msft · Accepted solution]

Hi @Zbig62 

I don't think you should use the “DAY” field in the "public Measurement" table as a slicer, please create a calendar table to use as a slicer. And don't create a relationship between the calendar table and the "public Measurement" table.

1. Create a calendar table and use it as a slicer.

Calendar = DISTINCT('public Measurement'[DAY])

 2. Replace the field “DAY” in your measure with the field “DAY” of calendar table.

DAY - 7 = 
CALCULATE(
    COUNT('public Measurement'[result]),
    FILTER(
        'public Measurement',
        'public Measurement'[DAY] = SELECTEDVALUE('Calendar'[DAY]) - 7
    )
)

Here is my simple test result:

By the way, when the day DAY-7 does not exist for the "DAY" in your data, the measure will return blank. For example, if you slicer selects 2024-02-01, DAY-7 is 2024-1-25, because the data for 2024-1-25 does not exist in your table, the measure returns blank.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Zbig62]

Hello
Thank you for your quick response. I have a small problem with this function. If I use it in a variable definition
I get the message SYNTAX ERROR

day - 7 =

a = DATEADD(SELECTEDVALUE('public Measurement'[DAY]),-7,day)

Return

CALCULATE(

COUNT('public Measurement'[result]); FILTER('public Measurement'; 'public  Measurement'[DAY] = a 

              )

        )

[SachinNandanwar · Accepted solution]

You have to change the filter in your expression

DAY - 7 =

CALCULATE (
    COUNT ( 'public Measurement'[result] ),
    FILTER (
        'public Measurement',
        'public  Measurement'[DAY]
            = DATEADD ( SELECTEDVALUE ( 'public Measurement'[DAY] ), -7, DAY )
    )
)

[Zbig62 · Accepted solution]

I have a problem with the type of parameters in and I don't know how to deal with it

[Zbig62]

I tried another approach and I also get a message about parameter type mismatch

[SachinNandanwar · Accepted solution]

Sorry I didnt realise that Dateadd function needs an exclusive date table.Your approach wouldnt work as you are trying to compare rows within the current row context and subtracting from the same value using a filter.

As suggested by @v-xianjtan-msft you need a date table. You can use the solution provided by @v-xianjtan-msft 

[SachinNandanwar]

DATEADD(SELECTEDVALUE('public Measurement'[DAY]),-7,day)