Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

How to calculate the date difference between a existing column with date slicer "selected value"

Hi ALL, 

 

I have a table with two date columns "ValueDate" and "EOMDate" (end of month date), I have taken the EOMDate in a date slicer and also able to capture the value selected from the slicer using below code with a Measure as below.

ValueSelected = CALCULATE(
MAX(Remittance[Remittance.EOMDate]),

ALLSELECTED(Remittance[Remittance.EOMDate]))


but I am unable to compare the measure "ValueSelected" with column "ValueDate". 

I tried this first 

AgeMeasure = DATEDIFF(Remittance[Remittance.ValueDate],Remittance[ValueSelected],DAY)

I got the below error

"A single value for column 'Remittance.ValueDate' in table 'Remittance' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I tired the below as an alternative approach, which is throughing the same error , any advise will be much helpful.. Hearty thanks. 

AgeMeasure = 
VAR MinVDate = MIN(Remittance[Remittance.ValueDate])
VAR MinVVD = DATEDIFF(MinVDate,Remittance[Remittance.ValueDate],DAY) // Getting the error Here 
VAR SVMVD = DATEDIFF(MinVDate,[ValueSelected],DAY)
RETURN
SVMVD - MinVVD
Need Help
  • MattAllington's avatar
    MattAllington
    7 years ago

    It's not clear to me what you are trying to do here.  What a slicer does is filter your table.  The best way to understand this is to load your data into Excel, convert it to a table and then place a filter on the EOM column for the date you want.  This is what a slicer does.  Now that the table is filtered, what do you want to do?  The table has a filter on it so you are limited in what you can do to the filtered copy of the table.

     

    So, still not really knowing what you want to do, it is likely that you will need 1 or 2 disconnected slicers that are on a table of dates. Click on this new slicer to harvest the value that the user selects.  This way you get the input without placing a filter on the table.

     

    If you can provide some more information about the logic of what you are trying to acheive I could possibly help some more

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Example Data

    EOMDateValueDateSelectedDate
    12/31/201212/06/201210/31/2016
    02/28/201302/21/201310/31/2016
    08/31/201408/18/201410/31/2016
    09/30/201409/14/201410/31/2016

     

    Expected Output

    EOMDateValueDateSelectedDateAgeValue
    12/31/201212/06/201210/31/20161425
    02/28/201302/21/201310/31/20161348
    08/31/201408/18/201410/31/2016805
    09/30/201409/14/201410/31/2016778

     

    Note:
    ValueDate: >> Column from the Source (SSAS Tabular model)

    SelectedDate / ValueSelected : >> a measure calculated based on the date slicer built on "EOMDate" column

      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        7 years ago

        It's not clear to me what you are trying to do here.  What a slicer does is filter your table.  The best way to understand this is to load your data into Excel, convert it to a table and then place a filter on the EOM column for the date you want.  This is what a slicer does.  Now that the table is filtered, what do you want to do?  The table has a filter on it so you are limited in what you can do to the filtered copy of the table.

         

        So, still not really knowing what you want to do, it is likely that you will need 1 or 2 disconnected slicers that are on a table of dates. Click on this new slicer to harvest the value that the user selects.  This way you get the input without placing a filter on the table.

         

        If you can provide some more information about the logic of what you are trying to acheive I could possibly help some more

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    Actually, you don't want to filter the data. You'd like to indicate the date instead. I would suggest you create a new table [EOMDate] that will provide the field of the slicer. 

    Please download the demo from the attachment.

    SelectedValue = SELECTEDVALUE('Table'[EOMDate])
    Measure =
DATEDIFF ( MIN ( 'Table1'[ValueDate] ), [SelectedValue], DAY )

    How-to-calculate-the-date-difference-between-a-existing-column-with-date-slicer-selected

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-jiascu-msft, 

       

      Thanks for your Great time..  it is working fine with "List" and "Dropdown" type of date slicer, but for my requirement I am using "Before" type of slicer where this "SelectedValue" is not working.. 

       

      unfortunately, I need to filter the data to get whatever data available Prior to the date (EOMDate) as per the selection

       

      any help is much & more appreciated.. 

       

      Thanks, 

      Prabhu

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous,

         

        It's my pleasure. The measure could be useful in many scenarios. Please try it out.

         

        SelectedValue = Max('Table'[EOMDate])

         

        Best Regards,
        Dale