Show Days Before and After max Date from Date-Slicer

[tim_m15]

Hi, folks!

My goal is to implement a Measure that works an a "Filter on this Fisual" with an logical-test (1 for ResultTrue and 0 for ResultFalse) that returns 1 in my Table with a result, that shows only the rows with Sales in this selected Date Range. 

First approach:

My first approach is I created a ParameterTable in my Dataset (Range 0-365 that means the Days for your understanding) with a related Measure max of this Range.

In my report I want to pick up this ParameterTable as an "Less than or equal to"-Date-Slicer-Filter and want to insert here a 0-365 Value for example 5.

Such as my MaxDate is 31.10.2019 and my Parameter in this Date-Slicer-Filter is 5 (Days), I only want to see only the rows in this Table from 31.10.2019 minus 5 Days (comes from[ParameterValueFromDataSet]) (26.10.2019) until 31.10.2019 plus 5 Days (5.11.2019)

This is not working:

Measure:

IF (ABS(DATEDIFF(LASTDATE(Zeit[Date]);FIRSTNONBLANK(all(Zeit[Date]);TRUE());DAY)<='[ParameterValueFromDataSet]);1;0) 

Second approach:

My second approach is to implement a second Date-Table in my Dataset that is filtered from my first DateTable (1-*). Then I go to my Report and set a Date-Slicer with the First-Date-Table with an between-range from 1.1.19 - 31.12.19 and a second Date-Slicer with my Second Date-Table to select here only 1 Date (such as 01.04.19).

Here, my "Filter on this Fisual"-Measure that works on a Table. It looks like:

Measure2 =

VAR selectedDate = CALCULATE(MAX(SecondDateTable[Date]);ALL(FirstDateTable[Date]))

RETURN

if(AND(MAX(FirstDateTable[Date]) >= SecondDateTable[Date] -[ParameterValueFromDataSet]; MAX(FirstDateTable[Date]) <= SecondDateTable[Date] +[ParameterValueFromDataSet]);1;0)

Example:

MAX(FirstDateTable[Date] = 31.12.2019

SecondDateTable[Date] = 01.04.2019

[ParameterValueFromDataSet] = 5

In the figurative sense:

Measure2 =

VAR selectedDate = 01.04.2019

RETURN

if(AND(Date>= 01.04.2019 minus 5 days that is in result 27.03.2019; Date <=01.04.2019 plus 5 days that is in result 06.04.2019 );1;0)

Here I want to shows only these rows wiht sales in my Table, that are between 27.03 - 06.04 although my Date Calender is showing more Days.

Thanks for your help.

[v-deddai1-msft · Accepted solution]

Hi @tim_m15 ,

Would you please show us some sample data or table structure?  According to your description, my understanding is that you have two slicer one for date , another one for ParameterValueFromDataSet. You want to show the values of a time period based on these two selected values If anything is misunderstood, please tell me.

I suggest you use the following measure :

Measure =

VAR selecteddate =

    SELECTEDVALUE ( SecondDateTable[Date] )

VAR p =

    SELECTEDVALUE ( ParameterValueFromDataSet[ParameterValueFromDataSet] )

RETURN

    IF (

        MAX ( FirstDateTable[Date] ) >= selecteddate - p

            && MAX ( FirstDateTable[Date] ) <= selecteddate + p,

        1,

        0

    )

I created some test data, you can refer to the pbix file : https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaTYxoZJG5VGoKewGV...

Best Regards,

Dedmon Dai

[v-deddai1-msft · Accepted solution]

Hi @tim_m15 ,

Would you please show us some sample data or table structure?  According to your description, my understanding is that you have two slicer one for date , another one for ParameterValueFromDataSet. You want to show the values of a time period based on these two selected values If anything is misunderstood, please tell me.

I suggest you use the following measure :

Measure =

VAR selecteddate =

    SELECTEDVALUE ( SecondDateTable[Date] )

VAR p =

    SELECTEDVALUE ( ParameterValueFromDataSet[ParameterValueFromDataSet] )

RETURN

    IF (

        MAX ( FirstDateTable[Date] ) >= selecteddate - p

            && MAX ( FirstDateTable[Date] ) <= selecteddate + p,

        1,

        0

    )

I created some test data, you can refer to the pbix file : https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EaTYxoZJG5VGoKewGV...

Best Regards,

Dedmon Dai

[tim_m15 · Accepted solution]

Hi Dedmon,

thanks for your reply and your tip looks helpful!

I could now solve this challenge myself. I implement a second Date Table as a Slicer as I have described and another Slicer with the Parametertable and write the following measure in a function of a visual-filter in a true-/false-Condition

Measure:

VAR SelectedDate = CALCULATE( MAX( Date[SecondDateTable] ); ALL( Date[FirstDateTable] ) )

RETURN
IF( AND( MAX(Sales[BookingDate] ) >=  SelectedDate - [ParameterTableValue] ; MAX(Sales[BookingDate]) <= SelectedDate + [ParameterTableValue] );
    1;
         0)

Thereafter I put the Sales and the BookingDate in my Table and filter the visual-filter with "1" and it works. 

Best Regards,

Tim