Forum Discussion
Dynamic previous row value?
Hi DataStraine ,
You issue is that you are forcing the calculation to be based on the month since you are using the dateadd.
What can be done is to make use of a swith formula and then change the scope of your measure something similar to:
Customer Count Prev. Month = SWITCH ( TRUE (), ISINSCOPE ( 'Calendar'[Month] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, MONTH ) ), ISINSCOPE ( 'Calendar'[Quarter] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, QUARTER ) ), ISINSCOPE ( 'Calendar'[Year] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, YEAR ) ) )also there is some blogs about custom periods and this type of calculations:
https://radacad.com/previous-dynamic-period-dax-calculation
https://www.daxpatterns.com/month-related-calculations/
Hi DataStraine ,
You issue is that you are forcing the calculation to be based on the month since you are using the dateadd.
What can be done is to make use of a swith formula and then change the scope of your measure something similar to:
Customer Count Prev. Month =
SWITCH (
TRUE (),
ISINSCOPE ( 'Calendar'[Month] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, MONTH ) ),
ISINSCOPE ( 'Calendar'[Quarter] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, QUARTER ) ),
ISINSCOPE ( 'Calendar'[Year] ), CALCULATE ( [Customer Count], DATEADD ( 'Calendar'[Date], -1, YEAR ) )
)
also there is some blogs about custom periods and this type of calculations:
https://radacad.com/previous-dynamic-period-dax-calculation
https://www.daxpatterns.com/month-related-calculations/
Thank you for your response. This solved my issues. For people who may be looking for the answer in the future, if you are looking at a current year or quarter that has not yet ended, you'll need to add ENDOFMONTH and ENDOFYEAR to your calculations to get the right previous counts.