previous month calculation

[Uzi2019]

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:

Previous Month Cost =

VAR PRev = CALCULATE(SUM(A[Cost]),DATEADD(A[Date],-1,MONTH),ALLEXCEPT(A,A[Brand],A[Date]))

VAR Curr = CALCULATE(sum(A[Cost]))

RETURN IFERROR((Curr-PRev)/PRev,"-")

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.

[v-jayw-msft · Accepted solution]

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

[amitchandak]

@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.

[Uzi2019]

@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?

[amitchandak]

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

[Uzi2019]

Not getting your solution. Can you please share the pbix file? I have shared some sample data also in my first post.