Forum Discussion
previous month calculation
Hi Experts,
I want to calcuate previous month cost in a Matrix visual in which I need to show this previous month cost against the Date and Brand Column. Everything is working fine but if brand of one particular date is missing for previous month then it is showing error of continous date (can't display the visual).
Is there any work around of such situation where we can handle missing brand of previous month?
I used below DAX:
Sample Data:
| Brand | Date | Cost |
| AAA | 1-Jan-20 | 100 |
| AAA | 2-Jan-20 | 200 |
| AAA | 3-Jan-20 | 300 |
| BBB | 1-Jan-20 | 400 |
| BBB | 2-Jan-20 | 100 |
| AAA | 1-Feb-20 | 300 |
| AAA | 2-Feb-20 | 500 |
| AAA | 3-Feb-20 | 100 |
| BBB | 1-Feb-20 | 150 |
| BBB | 3-Feb-20 | 250 |
I urge you to please help me to get this done.
Hi Uzi2019 ,
You could use EDATE() function instead.
Modify the formula as below.
Previous Month Cost = VAR PRev = CALCULATE(SUM('Table'[Cost]),EDATE('Table'[Date],-1),ALLEXCEPT('Table','Table'[Brand],'Table'[Date])) VAR Curr = CALCULATE(sum('Table'[Cost])) RETURN IFERROR((Curr-PRev)/PRev,"-")If the result is not what you want, please show the expected result to us.
Best Regards,
Jay
5 Replies
Uzi2019 , Please use date table for that. Join date of date table with date of your table
Previous Month Cost =
VAR PRev = CALCULATE(SUM(A[Cost]),DATEADD(Date[Date],-1,MONTH),ALLEXCEPT(A,A[Brand],A[Date]))
VAR Curr = CALCULATE(sum(A[Cost]))
RETURN IFERROR((Curr-PRev)/PRev,"-")refer my video, why time intelligence fails - https://www.youtube.com/watch?v=OBf0rjpp5Hw
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.
amitchandak - there is no issue with Date. The Date has continuous data but channel is missing against some dates. One more thing, with my shared measure all work perfect until I add channel column in that matrix.
I can not Calendar table for my scenario. I'll to take everything from same table.
So do you have any workaround without using Calendar table?
Uzi2019 , check if this can work
Previous Month Cost =
var _date = date(year(A[Date]),month(A[Date])-1,date(A[Date]))
VAR PRev = CALCULATE(SUM(A[Cost]),_date,ALLEXCEPT(A,A[Brand],A[Date]))
VAR Curr = CALCULATE(sum(A[Cost]))
RETURN IFERROR((Curr-PRev)/PRev,"-")of use a filter clause
filter(A, A[Date] =_date)
Hi Uzi2019 ,
You could use EDATE() function instead.
Modify the formula as below.
Previous Month Cost = VAR PRev = CALCULATE(SUM('Table'[Cost]),EDATE('Table'[Date],-1),ALLEXCEPT('Table','Table'[Brand],'Table'[Date])) VAR Curr = CALCULATE(sum('Table'[Cost])) RETURN IFERROR((Curr-PRev)/PRev,"-")If the result is not what you want, please show the expected result to us.
Best Regards,
Jay
