How to get nonselected value from a slicer range

[Anonymous]

Hello All,

I have passed year,month,day in heirarchy slicer from Date table. Date table has a date range from 01-01-2015 to 12-31-2060.

Passing below measure on slicer (filters Pane) I have restricted it to 2020 and 2021.

YearCheck =

var loaddate = max('Brand Attributes'[insert_timestamp])

return

IF((SELECTEDVALUE('Time Dimension'[Date].[Year])=YEAR(TODAY())&& SELECTEDVALUE('Time Dimension'[Date].[Date]) <=loaddate)

|| SELECTEDVALUE('Time Dimension'[Date].[Year])=YEAR(TODAY())-1,1)

now I want to derive a measure which should display the non selected value from slicer if i deselect any month from slicer. but result should be within 2020 & 2021 (within Slicer Range)

Please help @coco @amitchandak @PBI @PBICommunity @Fowmy 

[Anonymous · Accepted solution]

Found the solution

created a calculated column 

CurrentYear_Chart =

var loaddate=MAX('Brand Attributes'[insert_timestamp])

return IF(AND(Year('Time Dimension'[Date])=YEAR(TODAY()),'Time Dimension'[Date]<=loaddate) ||'Time Dimension'[Date]<=EOMONTH(loaddate,2) || Year('Time Dimension'[Date])=YEAR(TODAY())-1,1)

passed it as a visual filter

then created a flag 

futuremonths =

var no_of_months=CALCULATE(CALCULATE(DISTINCTCOUNT('Time Dimension'[month_name]),ALL('Time Dimension'[Start of Month],'Time Dimension'[StartOfMonth])),ALL('Time Dimension'[CurrentYear_Chart]),'Time Dimension'[Year]=YEAR(TODAY()))

var curr_year =MAX('Time Dimension'[Date].[Year])

return

IF( LASTDATE('time dimension'[Start of Month]) <= LASTDATE('Product Attributes'[insert_timestamp])||(no_of_months=12 && LASTDATE('time dimension'[Start of Month]) <=EOMONTH( LASTDATE('Product Attributes'[insert_timestamp]),2)),1,IF(curr_year=YEAR(TODAY())&& no_of_months<12 && MONTH(SELECTEDVALUE('time dimension'[StartofMonth])) <=MONTH(LASTDATE('Product Attributes'[insert_timestamp])),1))

passed it as visual filter

[Anonymous]

// I can't open your files as I'm
// behind my company's firewall and
// can't download or open files from
// Google Drive.

// Say that you have a date table and
// you have a slicer that displays
// Months. To get the last selected
// Month you'd write this measure:

[Last Selected MonthID] = MAX( Dates[MonthID] )

// To get the first selected month
// you'd write:

[First Selected MonthID] = MIN( Dates[MonthID] )

// Dates[MonthID] is a unique number
// that is assigned to months and it's
// unique across all years. So, the first
// month you have in your table should be
// 1 and move up by 1 every time you
// encounter a new month. So, the last
// MonthID should be equal to the number of
// all months you've got in your calendar.

// The problem with "I want to derive a measure
// which should display the non selected value
// from slicer if i deselect any month from slicer."
// is that a measure can return only a scalar
// value, not a table. So, if you deselect more
// than 1 month, such a measure out of necessity
// will return BLANK. Say that out of all months
// you've deselected only 1 month from the slicer
// and want to return the ID of this month.
// Then you write:

[Deselected MonthID] =
var DeselectedMonthIDs =
    EXCEPT(
        ALL( Dates[MonthID] ),
        DISTINCT( Dates[MonthID] )
    )
var Result =
    if( COUNTROWS( DeselectedMonthID ) = 1,
        DeselectedMonthID
    )
return
    Result
    
// The assumption is that you store only relevant
// periods of time in your table, not something
// like 60 years ahead. To manage the number of years
// and the start and end of the date table, you can
// and, indeed, should use Power Query parameters.

[Anonymous · Accepted solution]

Found the solution

created a calculated column 

CurrentYear_Chart =

var loaddate=MAX('Brand Attributes'[insert_timestamp])

return IF(AND(Year('Time Dimension'[Date])=YEAR(TODAY()),'Time Dimension'[Date]<=loaddate) ||'Time Dimension'[Date]<=EOMONTH(loaddate,2) || Year('Time Dimension'[Date])=YEAR(TODAY())-1,1)

passed it as a visual filter

then created a flag 

futuremonths =

var no_of_months=CALCULATE(CALCULATE(DISTINCTCOUNT('Time Dimension'[month_name]),ALL('Time Dimension'[Start of Month],'Time Dimension'[StartOfMonth])),ALL('Time Dimension'[CurrentYear_Chart]),'Time Dimension'[Year]=YEAR(TODAY()))

var curr_year =MAX('Time Dimension'[Date].[Year])

return

IF( LASTDATE('time dimension'[Start of Month]) <= LASTDATE('Product Attributes'[insert_timestamp])||(no_of_months=12 && LASTDATE('time dimension'[Start of Month]) <=EOMONTH( LASTDATE('Product Attributes'[insert_timestamp]),2)),1,IF(curr_year=YEAR(TODAY())&& no_of_months<12 && MONTH(SELECTEDVALUE('time dimension'[StartofMonth])) <=MONTH(LASTDATE('Product Attributes'[insert_timestamp])),1))

passed it as visual filter

[Anonymous]

this we need to keep hierarchy slicer as it is there in pbix file 

1. If both year (complete year) is selected then 15 months data which includes last 12 months+ current month+next months 

1. If only 2021 is selected then start of year to current month+ next 2 months 

1. If after selecting both year (complete year) we deselect any of the month then last 15 months from previous months .

Example- if we deselect June 2021 then range of 15 months should start from May 2021 and should 15 months back upto March 2020.

Still any confusion please connect with me. 

Fact table has prev year sales & current year sales columns alongwith date column which is connected with date table which has data from 2018 to 2023. need to show comparision of current year sales vs prev year sales on bar chart

[Anonymous · Accepted solution]

created a calculated column 

CurrentYear_Chart =

var loaddate=MAX('Brand Attributes'[insert_timestamp])

return IF(AND(Year('Time Dimension'[Date])=YEAR(TODAY()),'Time Dimension'[Date]<=loaddate) ||'Time Dimension'[Date]<=EOMONTH(loaddate,2) || Year('Time Dimension'[Date])=YEAR(TODAY())-1,1)

passed it as a visual filter

then created a flag 

futuremonths =

var no_of_months=CALCULATE(CALCULATE(DISTINCTCOUNT('Time Dimension'[month_name]),ALL('Time Dimension'[Start of Month],'Time Dimension'[StartOfMonth])),ALL('Time Dimension'[CurrentYear_Chart]),'Time Dimension'[Year]=YEAR(TODAY()))

var curr_year =MAX('Time Dimension'[Date].[Year])

return

IF( LASTDATE('time dimension'[Start of Month]) <= LASTDATE('Product Attributes'[insert_timestamp])||(no_of_months=12 && LASTDATE('time dimension'[Start of Month]) <=EOMONTH( LASTDATE('Product Attributes'[insert_timestamp]),2)),1,IF(curr_year=YEAR(TODAY())&& no_of_months<12 && MONTH(SELECTEDVALUE('time dimension'[StartofMonth])) <=MONTH(LASTDATE('Product Attributes'[insert_timestamp])),1))

passed it as visual filter

[Anonymous]

I think you need to watch this: Show last 6 months based on user single slicer selection - SQLBI

Take a good look at the technique Alberto presents.

[Anonymous]

In the above video reference date (max date) is a key parameter which can only be derived if we get to know the first date of deselected month that is why i want a measure which should return a date of least unselected month.

[Anonymous]

Please have a look at sample file

https://drive.google.com/file/d/1_XXUBsXdQVPf3SzR4NykKmTKR4dwhdfQ/view?usp=sharing

[Anonymous]

@Anonymous 

First and foremost, you should have a proper calendar in your model and never, ever rely on the automatically generated datetime hierarchy. I mean: NEVER.

This notation 'Table'[Date].[XXX] should never happen in your model, either. It's simply wrong on too many levels. All the periods you need in your report must be created in columns of a dedicated Date/Calendar table.

By the way, to load a specific Date range into your calendar you can use Power Query parameters. No idea why you'd like to store dates until 2060. It only unnecessarily takes space.

You should not complicate things that can be done easily.

"now I want to derive a measure which should display the non selected value from slicer if i deselect any month from slicer"

Where do you want to display it? In what format? What should happen if you deselect more than 1 month or all months? What if you deselect just several days in a month? Can you be more specific with your questions, please?

[Anonymous]

If we deselect more than 1 month then datarange should start from previous month of minimum deslected month (if march and april both are deslected the datarange should be Feb -15 months) there will not be any option to deselect any days

[Anonymous]

In slicer I have 2 years (2020 & 2021) if whole 2020 & 2021 is selected then it should should show total 15 bars (Current Month -12 months and also next 2 months) like below

If any of the month is deselected  (both 2020 & 2021 is selected) then it show 15 bars (previous month from deselected month also last 15 months) like below (in this June 2021 is deselected then last 15 months data)

If whole 2021 is selected then (June is current Month)

If whole 2021 is selected and june (Current Month) unselected then 

@amitchandak @PBICommunity @Anonymous @Fowmy Please help