Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi experts,
i have to calculate the total sales amount.
both yeartodate & monthtodate.
i.e., i have the month field & year field as
i have a pivot table as well to calculate the required fields.
when i select on a particular month, for ex if i click on Jan 2015, MTD sales field should change but the YTD sales field shouldn't change.
when i tried, both are getting changed when a particular month got selected.
i have the following formulas
MTD sales field = Sum({< [SDate] = {'>=$(=Date(vThisMonthStart))<=$(=Date(vMaxSalesDate))'} >} sales)
YTD sales field = Sum({< [SDate] = {'>=$(=Date(vThisYearStart))<=$(=Date(vMaxSalesDate))'} >} SPAMT)
also, i have the following variables.
vThisYearStart =YEARSTART(vMaxSalesDate)
vThisMonthStart =MONTHSTART(vMaxSalesDate)
vMaxSalesDate =DATE(MAX(SDate))
vLastMonthStart =MONTHSTART(AddMonths(vMaxSalesDate, -1))
vLastMonthEnd =MONTHEND(AddMonths(vMaxSalesDate, -1))
also, i need previous month total sales. i.e., when i select feb 2015, MTD Sales field should display current month to date (selected month) total sales and Prev Month Sales field should display previous month(previous of selected month) total sales (whole month)
i have the calculation for previous month as
Prev Month Sales field = Sum({< [SDate] = {">=$(=Date(vLastMonthStart))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))"} >} sales)
when i tried these, its not showing expected results.
please help me out