cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Show Days Before and After max Date from Date-Slicer

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Show Days Before and After max Date from Date-Slicer

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

View solution in original post

Highlighted
Frequent Visitor

Re: Show Days Before and After max Date from Date-Slicer

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

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: Show Days Before and After max Date from Date-Slicer

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

View solution in original post

Highlighted
Frequent Visitor

Re: Show Days Before and After max Date from Date-Slicer

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

View solution in original post

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors