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

     

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?