Forum Discussion
DAX Calculation for Prior Period Date Range
Period Column is broken up into 2 month increments. I need to compare values vs the previous period and am not sure the DAX Formula. Below is what I have in PBI and then what I am looking to create. After this I need to show the Variance. (period is based of a Date Column)
13 Replies
mcornfield , if you have date range preferred from a joined date table
same period based on date range
Last Period =
var _max =maxx(date,date[date])
var _min =maxx(date,date[date])
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]<=_maxX &&date[date]>=_minX))or refer
How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg
Thanks, So Everything is in one table. the columns I have are Date, Year, Month Period, Period Number, and # Acheived. SO I am not sure how to implement your suggestion.
mcornfield , prefer to have date table, "all" does not go fine when what do display row level data.
You can replace the date table with your table and check
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi mcornfield ,
Please try the following formula:
Measure = var pre_date = FILTER( ALL('Table'), 'Table'[QuestionEndDate] < max('Table'[QuestionEndDate]) ) var lastPeriod = MAXX( pre_date, 'Table'[QuestionEndDate] ) return CALCULATE( SUM('Table'[1. # Achieved]), filter( all('Table'), 'Table'[QuestionEndDate] = lastPeriod ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works in the one view but when i try to expand by other dimensions it just repeats the total.
Please see image for reference. In my live file I have other dimensions I have to bring in.
Hi mcornfield ,
After adding other dimensions, if you want to calculate the value for the same day 2 months ago, then try the following formula. For example when [2. Answer Date] column is "2021-8-30", then calculate the [1. # Achieved] from "2021-6-30".
The same day 2 months ago = var _end = EDATE ( MAX ( 'Table'[QuestionEndDate] ), -2 ) var _start = EDATE ( MAX ( 'Table'[QuestionEndDate] ), -4 ) var PreviousPeriod = EDATE ( MAX ( 'Table'[2. Answer Date] ), -2 ) var sub = CALCULATE ( SUM ( 'Table'[1. # Achieved] ), filter ( ALL ( 'Table' ), 'Table'[2. Answer Date].[Date] = PreviousPeriod && 'Table'[2. Answer Date] <> BLANK() ) ) var total = CALCULATE ( SUM ( 'Table'[1. # Achieved] ), filter ( ALL ( 'Table' ), 'Table'[QuestionEndDate] > _start && 'Table'[QuestionEndDate] <= _end ) ) return IF ( ISFILTERED ( 'Table'[2. Answer Date] ), sub, total )If you want to roll back [1. # Achieved] from the previous 2 months, then try the following formula. For example when [2. Answer Date] is "2021-7-29", then calculate [1. # Achieved] from "2021-5-1" to "2021-5-29".
Rollback two months = var _start = EDATE ( MAX ( 'Table'[QuestionEndDate] ), -4 ) var PreviousPeriod = EDATE ( MAX ( 'Table'[2. Answer Date] ), -2 ) return CALCULATE ( SUM ( 'Table'[1. # Achieved] ), filter ( ALL ( 'Table' ), 'Table'[QuestionEndDate] > _start && 'Table'[2. Answer Date].[Date] <= PreviousPeriod ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
