Forum Discussion

lokeshpatel's avatar
lokeshpatel
Frequent Visitor
2 years ago

Change in Value calculation by slicer

Hello,

 

I am have interesting issue regarding change in value. The intended action would be for users to select two dates in the slicer (in red) and have the change amount reflect in the card (in green).

The measure works for opportunities that have records in the dates. However, when there is no record, it does not work (since there's no difference in dates).

 

The opportunity row has a Snapshot date record and it's connected to a date table of Snapshot date

 

The measures:

 

Try 1

Change =
CALCULATE(
    CALCULATE(
        sum(Table[Amount]),
        FILTER(Table, Table[Snapshot Date] = max(Table[Snapshot Date])))
-
    CALCULATE(
        sum(Table[Amount]),
        FILTER(Table, Table[Snapshot Date]= min(Table[Snapshot Date])
)
))

 

Try 2

Change 2 =
var varCount = COUNTROWS('Snapshot Date') //could use this to look at how many if needed
var varMindate = Calculate(FIRSTNONBLANK('Snapshot Date'[Snapshot Date],'Snapshot Date'[Snapshot Date]))
var varMaxdate = Calculate(LASTNONBLANK('Snapshot Date'[Snapshot Date],'Snapshot Date'[Snapshot Date]))
var varFirstdate = CALCULATE(MAX('Snapshot Date'[Snapshot Date]), ALLSELECTED('Snapshot Date'))
var varMinAmount =   CALCULATE(
        sum(Table[Amount]),
        FILTER(Table, Table[Snapshot Date] = varMindate))
var varMaxAmount = if(varMindate <> varMaxdate, CALCULATE(
        sum(Table[Amount]),
        FILTER(Table, Table[Snapshot Date] = varMaxdate)),0)

Return

IF( ISBLANK(varMaxAmount - varMinAmount), 0, varMaxAmount - varMinAmount)
 
Works for the a record in the first but not in the second since I'm using the if statement

 

 

 Doesn't work for no record in the first date

 

Thanks in advance!

Need Help

2 Replies

  • NaveenGandhi's avatar
    NaveenGandhi
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi lokeshpatel 

     can you try below measure. The problem i can see is, if there is no record, The max date and min date are the same. Thus the value turns out 0. i.e 08-12-2024 and 20-12-2024 is selected and 20-12-2024 does not have a amount. now 08-12-2024 is taken as max and min and resutls in 0.

    TryChange = 
Var Maxdate = max('Table'[Date])
Var Mindate = min('Table'[Date])
Var Calc = CALCULATE(
        SUM('Table'[Amount]),
        'Table'[Date] = max('Table'[Date])
    )
    -
    CALCULATE(
        SUM('Table'[Amount]),
        'Table'[Date] = min('Table'[Date])
    )
RETURN SWITCH(TRUE(),Maxdate=Mindate,-sum('Table'[Amount]),Calc)

      If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

    Follow me on LinkedIn!!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  lokeshpatel ,

     

    Is your problem solved, if it is solved you can mark the correct answer as a marker, if not, you can provide more details and we can help you better!

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.