difference from previous Friday

[RobRayborn]

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.

 

[RobRayborn]

Thank you so much for the assitance!  This worked perfectly.

[FreemanZ]

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]

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:

[Jihwan_Kim · Accepted solution]

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]

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?