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 difference ends up being Zero. So I end up with only four days. 
I need to subtract Monday from the previous Friday amount.  
I have a standard Date Table with offsets and "IsWeekday" type columns. 

The blue highlight below shows the amount on prevous Friday. The yellow highlight shows the % amount diff from previous day, or DATEADD, -1, DAY.

 

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 )
    )

     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    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 )
    )

     

    • RobRayborn's avatar
      RobRayborn
      Helper IV
      3 years ago

      This does what I asked for it to do, however it seems to have broken the TOTALYTD and DATESYTD measures.  I cannot produce a Rolling Annual amount with this solution.  Any ideas on how to fix this?

  • 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: