Forum Discussion

mohammadrafic's avatar
mohammadrafic
Helper I
5 years ago
Solved

calendar selectedvalue compare another table

  hi , i have calendar table and used in below calendar slicer. how can i get the selectedvalue which is highlight in red.  and it shoud compare with another table date column which is available or...
Need Help
  • ITManuel's avatar
    ITManuel
    5 years ago

    Hi  ,

     

    I tried the following:

    - Calendar is your Calendar table

    - Calendar1 is your other table which contains the dates to check existence in your selection

     

     

    1. Create measure to return Selected Max Date 

     

     

    SelectedMaxDate = 
VAR SelectedMaxDate = 
    CALCULATE(
        MAX('Calendar'[Date]),
        ALLSELECTED('Calendar')
    )
RETURN
SelectedMaxDate

     

     

     

    1. Create measure to check weather Selected Max Date is part of Calendar1. If yes, return "" otherwise return "Error".

     

     

    CheckDate = 
VAR CheckDate = 
    CONTAINSROW(VALUES(Calendar1),[SelectedMaxDate])
VAR Result =
    IF(
        CheckDate,
        "",
        "Error"
    )
RETURN
    Result

     

     

     

    PBiX file under the link below:

    https://we.tl/t-GDrBRxvXYC

     

    Best regards

     

amitchandak's avatar
amitchandak
Super User
5 years ago

mohammadrafic ,

In a measure you can get the max date using

selectedvalue(Date[Date])

 

or

Maxx(allselected(Date), Date[date])

mohammadrafic's avatar
mohammadrafic
Helper I
5 years ago

it is bring end of the year date...it is not working .. and i need to compare that date with another table date is available or not.

 

 

 

 

or can you tell me how to hide the future dates from the abve pls

  • ITManuel's avatar
    ITManuel
    Responsive Resident
    5 years ago

    Hi  ,

     

    I tried the following:

    - Calendar is your Calendar table

    - Calendar1 is your other table which contains the dates to check existence in your selection

     

     

    1. Create measure to return Selected Max Date 

     

     

    SelectedMaxDate = 
VAR SelectedMaxDate = 
    CALCULATE(
        MAX('Calendar'[Date]),
        ALLSELECTED('Calendar')
    )
RETURN
SelectedMaxDate

     

     

     

    1. Create measure to check weather Selected Max Date is part of Calendar1. If yes, return "" otherwise return "Error".

     

     

    CheckDate = 
VAR CheckDate = 
    CONTAINSROW(VALUES(Calendar1),[SelectedMaxDate])
VAR Result =
    IF(
        CheckDate,
        "",
        "Error"
    )
RETURN
    Result

     

     

     

    PBiX file under the link below:

    https://we.tl/t-GDrBRxvXYC

     

    Best regards

     

    • mohammadrafic's avatar
      mohammadrafic
      Helper I
      5 years ago

      ITManuel Great !!. it is working when 

      Calendar = CALENDAR(minx('WSummary',[START]),date(2021,12,31))

      but i need this condition

      Calendar = CALENDAR(minx('WSummary',[START]),'WSummary',[END])
      i need to display the last available date by default when i open the report but it is not happening it is opening with 12/31/2021. please check if you have any idea. like 8/23/2021
      • ITManuel's avatar
        ITManuel
        Responsive Resident
        5 years ago

        Try to refresh your model after opening. The slicer will consider the last date available in your model.

        You can try to change the MaxDate of Calendar in the sample file I have sent. The slicer is considering always the first and last date of Calendar.