Forum Discussion
Power BI Total
Hi,
I have created measures for Current Year Sales vs Last Year Sales.
For Current Year:
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.
6 Replies
- lbendlin
Super User
Using SELECTEDVALUE inside a CALCULATE is asking for trouble. You will need to add a "IsPastInPreviousYear" flag to your calendar table to indicate which timeframe should be considered. Then the measure would be
LY Sales = CALCULATE([Sales], SAMEPERIODLASTYEAR('Dates'[Date]),'Dates'[IsPastInPreviousYear])
- v-chenwuz-msft
Community Support
Hi Anonymous ,
Is that you only have one sale table?
If yes, it is strange that LY Sales won't get the result when you select 2022 unless the [Sales] has some function like all() or removefilter() in its code.
If I'm wrong, you can try using the following measure.
LY Sales = VAR _start = DATE( MAX( sales[year] ) - 1, MIN( 'sales'[month] ), 1 ) VAR _end = DATE( MAX( 'sales'[year] ) - 1, MAX( 'sales'[month] ) + 1, 1 ) - 1 RETURN CALCULATE( SUM( 'sales'[sales] ), FILTER( ALL( table ), [dates] >= _start && [dates] <= _end ) )If you do not have [dates] in sales table, please create dates column by [year], [month], [day]
Or please share sample data and expect result, if you need more help.
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.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file.
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.
- v-chenwuz-msft
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.
