Forum Discussion

mohammadrafic's avatar
mohammadrafic
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
      Icon for Responsive Resident rankResponsive 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
        Icon for Helper I rankHelper 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