Forum Discussion
subtraction same colum
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
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)Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- Greg_DecklerCommunity Champion
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 - amitchandakSuper User
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos. - westwrightjResolver III
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!
- JcastillRegular Visitor
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)returnIF(VarThis - LastYear = 0, BLANK(), VarThis - LastYear)but the result is blank😞
- V-lianl-msftCommunity Support
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)Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.