Forum Discussion

GuiBrum's avatar
GuiBrum
Frequent Visitor
6 years ago
Solved

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     ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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.
     

GuiBrum's avatar
GuiBrum
Frequent Visitor
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.