Forum Discussion
Sales Past Years to Date
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.
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.
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.