Need help to Display future date columns from slected date range

[Mahi1827]

Hi,

Need help on below dates requirement in power bi desktop.

I have 2 date columns like below(mm/dd/yyyy), as per the selected daterange i would like to show only upcoming date columns from selcted max date. "selected date" column created from Date table, "up coming dates" column fetching from database table. as below mentioned format

selected date, upcoming dates

5/1/2020         6/1/2020

5/5/2020        7/5/2020

4/15/2020     5/8/2020

5/302020     8/20/2020

3/10/2020      4/10/2020

when i choose "selcted date" column for date slicer and selected date range as 5/1/2020 to 5/10/2020 then i am looking for upcoming dates > 5/10/2020 ( which is max date from selected date range) and expecting below output in table visual

selected date  upcoming date

5/1/2020         6/1/2020

5/5/2020        7/5/2020

5/302020      8/20/2020

i have tried by first step creating measure for selected max date=max(Date(selected date))

and second step as created new column like below by using above measure

upcoming date= if( upcoming date>selected max date,upcomingdate,blank())

and third step as by adding new upcoming date column into table view but upcoming date results are not displayed as expected.

please suggest and help to get solution.

Thank you,

Mahi18

[V-lianl-msft · Accepted solution]

Hi @Mahi1827 ,

Try this measure:

Measure 3 =
VAR max_date =
    CALCULATE (
        MAX ( 'Table 2'[selected date] ),
        ALLSELECTED ( 'Table 2'[selected date] )
    )
VAR next_date =
    CALCULATE (
        FIRSTNONBLANK ( 'Table (2)'[selected date], 1 ),
        FILTER ( 'Table (2)', 'Table (2)'[selected date] > max_date )
    )
RETURN
    IF (
        MAX ( 'Table (2)'[selected date] ) IN VALUES ( 'Table 2'[selected date] )
            || MAX ( 'Table (2)'[selected date] ) = next_date,
        1,
        0
    )

Sample .pbix

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

[Mahi1827]

Hi V-lianl-msft ,

 Thank you so much for your response, as you suggested i have tried by creating new measure and gone through the sample pbix file, in sample file all fields coming from one table..

but my data model like Date column is coming from Calender table "Date" which is used in date slicer.

selected date, upcloming date columns are coming from other table . so here relation is "selected date" mapped to "date" in Date table.

finally in date slicer i am taking "date" from date table.

for table view i am taking selected date, upcoming dates and  created new measure as you suggested  and added into filter section with value 1  but in result iam getting all the selected dates without considering max date from date slicer. unable get expected output.

could you please suggest me modified measure logic as per my data model

Thank you,

Mahi18

[V-lianl-msft]

Hi @Mahi1827 ,

You need to create an unrelated calendar table as a slicer, as in the sample file.

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

[amitchandak]

@Mahi1827 , Your date range in not filtering selected date, then why do not you use after option in date slicer upcoming date.

https://radacad.com/from-and-to-date-slicers-in-power-bi-filtering-based-on-two-fields

Or you can create a measure like this. Prefer independent date slicer

Measure =
var _max= maxx(allselected(Date), Date[Date])
return
calculate(count(Table[upcoming Date), filter(Allselected(Table),Table[upcoming date] >_max))

[Mahi1827]

Hi Amit,

Thank you for your quick response. sorry i missed the some of the part in my requirement. my updates are below

i am using only single date slicer for 2 reasons 1) is to display all dates in a table view in between selected date range .its working fine as regular.

2) when i use same date slicer i need to display upcoming dates only  from selected date range in separate table view . in my intial post i explained this one. so i am trying with measure or calculated column.

i have tried creating measure like below as per your suggestion

futuredate= var v_max=maxx(Allselected('Date'),Date'[date])

return

calculate(count(Table([upcomingdate]),filter(Allseelcted(Table),Table[[upcomingdate]>=v_max)

when i choose "selected date" column for date slicer and selected date range as 5/1/2020 to 5/10/2020 then i am looking for upcoming dates > 5/10/2020 ( which is max date from selected date range) and expecting below output in table visual.

selected date is from date slicer here .

selected date  future date

5/1/2020         6/1/2020

5/5/2020        7/5/2020

5/302020      8/20/2020

But when i use new created measure in table view i am getting the future dates count results only but I am expecting upcoming dates which are >=5/10/2020 as per above input

In below Date  and  futuredate displayed in table view (here date is from slicer,futuredate is taken from measure)

After applying measure iam getting results like below

date        future date

5/1/2020         2

5/5/2020         2

5/302020        2

In given measure i tried with "selected value" function in measure but its not returning futuredatecoulm.

I am looking for expected output like below

date           future date

5/1/2020         6/1/2020

5/5/2020        7/5/2020

5/302020      8/20/2020

Please look into this and help me with solution.

Thank you so much for your help in advance.

Thank you..

Mahi18

[Mahi1827]

Hi @amitchandak ,

I have tried creating measure like below, intially it give me the count of future dates,  but iam expecting upcoming date column deatils  which are >=5/10/2020 as per requirement

futuredate= var v_max=maxx(Allselected('Date'),Date'[date])

return

calculate(count(Table([upcomingdate]),filter(Allseelcted(Table),Table[[upcomingdate]>=v_max)

I have tried with changing the measure logic by below options  in return statement tried with 1) Allselected 2) Selectedvalue 3) HASONEVALUE 4)values 5)selectedcolumn options . ( tried all options but not getting exact output)

in all scenarios i am able to only one max date related to upclomingdate column. i am looking for below output as per the requirement

date           future date

5/1/2020         6/1/2020

5/5/2020        7/5/2020

5/302020      8/20/2020

Could you please look into this and help me with solution.

Thank you so much for your help in advance.

Thank you..

Mahi18