Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thank you so much for the assitance! This worked perfectly.
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:
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:
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 )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
105 | |
87 | |
73 | |
66 |
User | Count |
---|---|
122 | |
112 | |
98 | |
79 | |
72 |