Forum Discussion

AviramWeiss's avatar
AviramWeiss
Icon for Helper I rankHelper I
3 years ago
Solved

Returning values on slicer bounds

Hello all,

This is a basic question, but I wasn't able to solve it on my own or find a working solution.

I have a table containing date, name and value:

DateNameValue
Nov 1, 2022AAA20
Nov 2, 2022AAA30
Dec 1, 2022AAA40
Nov 5, 2022BBB10

 

I also have a date-period slicer, based on the same table. Let's say I slice Nov 1-30, 2022.

 

I want to show a result grid, with name, sum(value), value @ slicer minimum (in my example Nov 1) and value @ slicer maximum (Nov 30). If values are missing at these particular dates, show either 0 or blank (I don't care):

NameTotalStart valueEnd value
AAA50200 (Or Blank)
BBB100 (Or Blank)0 (Or Blank)

 

Obviously, I don't know the slicer choice in advance. It's up to the user to pick...

Tried to create measures with min/max using ALLSELECTED or KEEPFILTERS, and then add columns "First" and "Last" to the table, which are populated only when date=measure. Didn't work.

Ideas would be warmly welcomed.

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi AviramWeiss 

    Aha, the Date column of the slicer shall come from an independent Date Table with this code:

    DateTable = CALENDAR(MIN(TableName[Date]), MAX(TableName[Date]))
     
    And change the code for the measures to:
    StartValue =
    VAR MinDate = MIN (DateTable[Date])
    RETURN
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Date] = MinDate
    )
    EndValue =
    VAR MaxDate = MAX (DateTable[Date])
    RETURN
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Date] = MaxDate
    )
    Total =
    VAR MinDate = MIN (DateTable[Date])
    VAR MaxDate = MAX (DateTable[Date])
    RETURN
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Date] >= MinDate
             &&TableName[Date] <= MaxDate
    )
    i tried and it now worked like this:
     

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi AviramWeiss 

    try to plot a table visual of three measures with the code below:

    try like
     
    StartValue =
    VAR MinDate = MIN (TableName[Date])
    RETURN
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Date] = MinDate
    )
     
    EndValue =
    VAR MaxDate = MAX (TableName[Date])
    RETURN
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Date] = MaxDate
    )
     
    Total =
    VAR MinDate = MIN (TableName[Date])
    VAR MaxDate = MAX (TableName[Date])
    RETURN
    CALCULATE(
        SUM(TableName[Value]),
        TableName[Date] >= MinDate
             &&TableName[Date] <= MaxDate
    )
    • AviramWeiss's avatar
      AviramWeiss
      Icon for Helper I rankHelper I
      3 years ago

      Thank you, FreemanZ, for the quick and thorough response.
      I have tried your solution, but like all previous ones, it takes a "personal" max for every category.

      Thus, If I pick a min/max date that has no instance, the measure takes the first/last date within the selected range, and not the exact first/last date.
      Here's the data I entered again (all dates are dd/mm/yyyy format):

      When I pick Nov 2-7, I want to get for AAA startValue=30 and endValue=0, becuase it has no records at Nov 7. These are the results I got:

       

      Thank you very much again.

       

       

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        hi AviramWeiss 

        Aha, the Date column of the slicer shall come from an independent Date Table with this code:

        DateTable = CALENDAR(MIN(TableName[Date]), MAX(TableName[Date]))
         
        And change the code for the measures to:
        StartValue =
        VAR MinDate = MIN (DateTable[Date])
        RETURN
        CALCULATE(
            SUM(TableName[Value]),
            TableName[Date] = MinDate
        )
        EndValue =
        VAR MaxDate = MAX (DateTable[Date])
        RETURN
        CALCULATE(
            SUM(TableName[Value]),
            TableName[Date] = MaxDate
        )
        Total =
        VAR MinDate = MIN (DateTable[Date])
        VAR MaxDate = MAX (DateTable[Date])
        RETURN
        CALCULATE(
            SUM(TableName[Value]),
            TableName[Date] >= MinDate
                 &&TableName[Date] <= MaxDate
        )
        i tried and it now worked like this: