Sales Past Years to Date

[GuiBrum]

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.

[Anonymous]

@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.

[GuiBrum]

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.

[Anonymous · Accepted solution]

@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.