Forum Discussion
Power BI Total
Hi Anonymous ,
Every row has context for measure to calculate. So, we should modify the context via filter(all(table)....). at this point, we need condsider each month but also the total row. So get a range date of current row. For example, month 1 year 2021 , the start date of last year is date(2021 -1 ,1,1), the end date is date(2021-1,1,31). This is what var _start and var _end do.
But if your "startdate" type is Date, maybe you can try this code:
LY sales = CALCULATE(SUM('Table'[Sales]),all(),DATEADD('Table'[Date],-1,YEAR))
result:
You can refer the pbix in the attachment. if you need more help, please share your pbix file without sensitive date.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thank you for your response.
So, yes my all the related data are in "sales" table and I have created Year,Month & Day column from my "startDate" column. And my [Sales] measure have sum of subtotal nothing else.
And when I try your solution it gives me some numbers that is no where in the record of any Year or Month and is also not even a total of that all years, it would be great if you can explain that CODE?
Please find the attached picture of SELECTEDVALUE = 2021 & 2022 for refrence, this is what I get after I use your provided code for LY Sales.
Community SupportHi Anonymous ,
Every row has context for measure to calculate. So, we should modify the context via filter(all(table)....). at this point, we need condsider each month but also the total row. So get a range date of current row. For example, month 1 year 2021 , the start date of last year is date(2021 -1 ,1,1), the end date is date(2021-1,1,31). This is what var _start and var _end do.
But if your "startdate" type is Date, maybe you can try this code:
LY sales = CALCULATE(SUM('Table'[Sales]),all(),DATEADD('Table'[Date],-1,YEAR))
result:
You can refer the pbix in the attachment. if you need more help, please share your pbix file without sensitive date.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.