Forum Discussion

Amitkr174's avatar
Amitkr174
Helper III
3 years ago
Solved

Calculation based on Next Higher date along with a Condition

Hi - need your help on the below isssue. Condition is - if Createddate>CaseCompdate,then Createddate-CaseCompdate,else NULL. It should stop once Createddate>CaseCompdate is true, rest of the lines ...
Need Help
  • rbriga's avatar
    rbriga
    3 years ago

    This different solution worked for me:

    Output = 
VAR _Earliest =
    CALCULATE (
        MIN ( 'iPOV -MicroConversion'[CreatedDate] ),
        ALLEXCEPT('iPOV -MicroConversion','iPOV -MicroConversion'[CaseNumber]),
        KEEPFILTERS ( 'iPOV -MicroConversion'[CreatedDate] > 'iPOV -MicroConversion'[Case_Comp_date] )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] ) = _Earliest,
        DATEDIFF (
            SELECTEDVALUE ( 'iPOV -MicroConversion'[Case_Comp_date] ),
            SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] ),
            DAY
        ),
        0
    )
Amitkr174's avatar
Amitkr174
Helper III
3 years ago

Just wanted to know, when i am putting the measure in a Card it is showing me zero values. Is there a way to show the numbers?

rbriga's avatar
rbriga
Impactful Individual
3 years ago

The calculation of [Output] relies heavily on context, especially 

SELECTEDVALUE ( 'iPOV -MicroConversion'[Case_Comp_date] )
SELECTEDVALUE ( 'iPOV -MicroConversion'[CreatedDate] )

Both of which have no value until you select a row.

I a card, unless you've selected a row on the table, it has no context to calculate the dates.

 

In broad terms, if you have a default behavior in mind, you would have to recreate it in the measure to cover instances when no row is selected.

For example, replacing SELECTEDVALUE('iPOV -MicroConversion'[CreatedDate]) with MAX('iPOV -MicroConversion'[CreatedDate]).