subtraction same colum

[Jcastill]

Hello Community,

someone can help me. I am nes in BI

how can I do a subtract between 2 values on the same column (Matrix chart)

here my data in the matrix cha

[V-lianl-msft · Accepted solution]

Hi @Jcastill ,

You need a calendar table and create relationships with the fact table.

Then create measure like this:

Subtract Previous Year = 

var VarThis = SUM('Table'[value])

var LastYear =CALCULATE(SUM('Table'[value]),dateadd('Table 2'[date],-1,YEAR))

return

IF(
VarThis - LastYear = 0, VarThis, VarThis - LastYear)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[westwrightj]

Hey @Jcastill 

   Thanks for writing a post. I think I've got a solution to your issue, but without the underlying data I can't say for certain if this will work for you

First I've started out with these test data based on your snippet

Next I created a measure to take the max value and subtract if the previous years value. If the value is 0, we are going to show it as blank.

Subtract Previous Year = 

var VarThis = CALCULATE(MAX('Test Data'[Value]))

var LastYear = CALCULATE(MAX('Test Data'[Value]), 'Test Data'[Year] - 1)

return

IF(
VarThis - LastYear = 0, BLANK(), VarThis - LastYear)

Once I throw that value into the matrix, I added some condition formatted (I didn't choose visually appealing colors and I apologize for that blunder)

In this case we have a column for the actual value and to the right a column indicating the change for that month from the previous year.

Let me know if solution works for you or if we want to try and think of something else!

[Jcastill]

Hello,

thank you for your help. but I have the same problem.

I create a measure :

Subtract Previous Year =

var VarThis = CALCULATE(MAX(VW_ARAGING[Amount OVerdue]))

var LastYear = CALCULATE(MAX(VW_ARAGING[Amount OVerdue]), VW_ARAGING[Eff date].[Year] - 1)

return

IF(

VarThis - LastYear = 0, BLANK(), VarThis - LastYear)

but the result is blank

😞

[amitchandak]

@Jcastill , are you looking for column calculations. MOM , I doubt that is possible. You have to use Month formula and MOM to get this.

refer: https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Next month value =  CALCULATE(sum('table'[total hours value]),nextmonth('Date'[Date]))

last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
	


diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Greg_Deckler]

@Jcastill - If you want this in the Totals row, see this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907