Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
3 years ago
Solved

difference from previous Friday

I need to calculate the difference between the amount taken on a day and the amount taken on the previous day.  The problem is that on Satruday and Sunday the amount doesn't change, so on Monday my d...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    There are some ways to achieve this, and I think it depends on how the business process for Saturday and Sunday is defined. I assume there are blank (not zero) data on Sat and Sun, and one of ways to achieve this is using lastnonblank DAX function.

     

     

     

    Diff %: =
VAR _currentdate =
    MAX ( 'Calendar'[Date] )
VAR _lastnonblank =
    CALCULATE (
        LASTNONBLANK ( 'Calendar'[Date], [Value sum:] ),
        FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < _currentdate )
    )
VAR _lastnonblankvalue =
    CALCULATE ( [Value sum:], 'Calendar'[Date] = _lastnonblank )
RETURN
    IF (
        NOT ISBLANK ( [Value sum:] ) && HASONEVALUE ( 'Calendar'[Date] ),
        DIVIDE ( [Value sum:] - _lastnonblankvalue, _lastnonblankvalue )
    )

     

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi RobRayborn 

apparently Jihwan's solution works. 

 

I tried to simulate your case with such data:

 

verified by creating a table visual with data, value columns and a measure like:

measure = 
VAR _date = MAX(TableName[date])
VAR _value =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[date] = _date
    ),
    TableName[value]
)
VAR _datepre =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[date] < _date
    ),
    TableName[date]
)
VAR _valuepre =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[date] = _datepre
    ),
    TableName[value]
)
RETURN
DIVIDE(_value, _valuepre) -1

 

or like:

measure2 = 
VAR _date = MAX(TableName[date])
VAR _value =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[date] = _date
    ),
    TableName[value]
)
VAR _valuepre =
MAXX(
    TOPN(
        1, 
        FILTER(
            ALL(TableName),
            TableName[date] < _date
        ),
        TableName[date]
    ),
    TableName[value]
)
RETURN
DIVIDE(_value, _valuepre) -1

 

it worked like:

 

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    if you also accept calculated columns, try like:

    column = 
VAR _datepre =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[date] < EARLIER(TableName[date])
    ),
    TableName[date]
)
VAR _valuepre =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[date] = _datepre
    ),
    TableName[value]
)
RETURN
DIVIDE([value], _valuepre) -1

    or 

    column2 = 
VAR _valuepre =
MAXX(
    TOPN(
        1, 
        FILTER(
            ALL(TableName),
            TableName[date] < EARLIER(TableName[date])
        ),        
        TableName[date]
    ),
   TableName[value]
)
RETURN
DIVIDE([value], _valuepre) -1

     

    it worked like: