topic Re: Value 7 days earlier than selected in DAX Commands and Tips

Value 7 days earlier than selected

Zbig62 — Thu, 26 Sep 2024 19:08:30 GMT

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

)

Re: Value 7 days earlier than selected

SachinNandanwar — Thu, 26 Sep 2024 19:46:17 GMT

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

Re: Value 7 days earlier than selected

Zbig62 — Thu, 26 Sep 2024 20:19:35 GMT

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

)

Re: Value 7 days earlier than selected

SachinNandanwar — Thu, 26 Sep 2024 21:58:28 GMT

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 ) ) )

Re: Value 7 days earlier than selected

Zbig62 — Fri, 27 Sep 2024 05:05:57 GMT

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

Re: Value 7 days earlier than selected

Zbig62 — Fri, 27 Sep 2024 06:34:31 GMT

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

Re: Value 7 days earlier than selected

Anonymous — Fri, 27 Sep 2024 09:03:07 GMT

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.

Re: Value 7 days earlier than selected

SachinNandanwar — Fri, 27 Sep 2024 13:24:59 GMT

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 Anonymous you need a date table. You can use the solution provided by Anonymous