topic Sales Past Years to Date in Developer

Sales Past Years to Date

GuiBrum — Thu, 21 May 2020 14:54:44 GMT

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.

Re: Sales Past Years to Date

Anonymous — Fri, 22 May 2020 07:55:43 GMT

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.

Re: Sales Past Years to Date

GuiBrum — Fri, 22 May 2020 19:09:30 GMT

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.

Re: Sales Past Years to Date

Anonymous — Tue, 26 May 2020 06:54:19 GMT

GuiBrum

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.