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: Date Name Value Nov 1, 2022 AAA 2...
  • 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:
     
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:
 
  • AviramWeiss's avatar
    AviramWeiss
    Icon for Helper I rankHelper I
    3 years ago

    Works perfectly now. Thank you very much.