Forum Discussion
Sales Past Years to Date
Hello, I'm trying to set up a measure to show me the previous years sales to date.
tblSales: [Inv Date], [Inv Amount]
Year YTD Sales Amount
2016 4,000
2017 4,500
2018 4,800
2019 4,700 -> 2016-2019 showing the entire year, but I want it to show up to today's date for each respective year
2020 2,300 -> this is showing the data YTD
Thank you for the help.
Did you related the two table with columns Date[Date] and tblSales[Inv Date]? I guess you can filter with the [Inv Date] in the sales table instead of change the code to Date[Date], try stick it with the first filter. And you may replace Today() with 2020.05.20 to test.
Measure = Calculate( sum([Inv Amount]),FILTER(ALL(tblSales),[Inv Date]<=MAX([Inv Date])), FILTER(ALL(tblSales), SUMX(FILTER(tblSales, EARLIER(tblSales[Inv Date].[Year])=[Inv Date].[Year] && EARLIER(tblSales[Inv Date].[MonthNo])<=MONTH(DATE(2020,5,20)) && EARLIER(tblSales[Inv Date].[Day])<=DAY(DATE(2020,5,20))),1)))Could you provide a sample pbix if it still goes wrong
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
GuiBrum
Basically you need to calculate running total by Year, and filter Month and Day up to "Today()'s month and day". If that's the case, try the following measure:Measure = Calculate( sum([Inv Amount]),FILTER(ALL(tblSales),[Inv Date]<=MAX([Inv Date])), FILTER(ALL(tblSales), SUMX(FILTER(tblSales, EARLIER(tblSales[Inv Date].[Year])=[Inv Date].[Year] && EARLIER(tblSales[Inv Date].[MonthNo])<=MONTH(TODAY()) && EARLIER(tblSales[Inv Date].[Day])<=DAY(TODAY())),1)))Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you. I use a Date table so I incorportated it to the code, but the totals do not match.
Now all dates are showing comparable numbers which means that the filter is working, I dont know if the parameters are right though because the 2020 YTD is not matching the actual.
Sales YTD = Calculate( sum(tblSales[Inv Amount]),FILTER(ALL(tblSales),tblSales[Inv Date]<=MAX(tblSales[Inv Date])),FILTER(ALL(Dates),SUMX(FILTER(Dates,EARLIER(Dates[Date].[Year])=Dates[Date].[Year]&& EARLIER(Dates[Date].[MonthNo])<=MONTH(TODAY())&& EARLIER(Dates[Date].[Day])<=DAY(TODAY())),1)))My last record is 05/20/2020 if this means anything.thanks you for any further comments to help.Did you related the two table with columns Date[Date] and tblSales[Inv Date]? I guess you can filter with the [Inv Date] in the sales table instead of change the code to Date[Date], try stick it with the first filter. And you may replace Today() with 2020.05.20 to test.
Measure = Calculate( sum([Inv Amount]),FILTER(ALL(tblSales),[Inv Date]<=MAX([Inv Date])), FILTER(ALL(tblSales), SUMX(FILTER(tblSales, EARLIER(tblSales[Inv Date].[Year])=[Inv Date].[Year] && EARLIER(tblSales[Inv Date].[MonthNo])<=MONTH(DATE(2020,5,20)) && EARLIER(tblSales[Inv Date].[Day])<=DAY(DATE(2020,5,20))),1)))Could you provide a sample pbix if it still goes wrong
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
