Sum of sales of previous year MTD

[satish25]

Hello All,

I want the Expression for the 'Previous Year Month to Date'

However if I select a particular month, it should show me the Value From the

         'Start of the Previous Year to the End of the Selected  Month'.

E.g :      This Year Being 2018, if i Select the Month May, it should show me the values .i.e Sum(Sales) from                    1st Jan 2017 to 31st May 2017.

          Date Field in the Data is 'Order_Date'.

[OmarBenSalem]

sum({< DATE=,Year=,Month=,Quarter=,DATE={">=$(=YearStart(addYears(max(DATE),-1)))<=$(=addYears(max(DATE),-1))"}>}YourMeasure)

and please refer to this thread in which I tried to explain this step by step:

YTD, MTD issue

[sunny_talwar]

So when nothing is selected you want to see 12/01/2017 till 12/31/2017 (Previous MTD) and if May is selected then you would want to see 01/01/2017 till 05/31/2017? right?

Try this

{<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & MonthStart(Today(), -1) & '<' & MonthStart(Today()), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>}

[satish25]

Hi Sunny,

Sorry for the late response, was caught up in some other work

Actually I forgot to tell you that suppose I have a

Measure : Budget_Sales,

And I want to find the Sum(Budget_Sales), using the above logic, what changes would be needed in the equation ??

Sorry once again for the late reply

[sunny_talwar]

There was not aggregation in the above set analysis I gave you... may be you want this

Sum({<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & MonthStart(Today(), -1) & '<' & MonthStart(Today()), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>} Budget_Sales)

[satish25]

Thanks a Lot Sunny.

On selecting a particular Date this works perfectly

But when no selections are made, the data is shown of the current year,

I want the data to be shown of the previous year

[sunny_talwar]

So when nothing is selected, you would want to see Dec 1st 2017 to Dec 31st 2017? May be this

Sum({<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & MonthStart(Today(), -13) & '<' & MonthStart(Today(), -12), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>} Budget_Sales)

if you want Jan 1st 2017 till Dec 31st 2017 without selection, then try this

Sum({<Date = {"$(=If(GetSelectedCount(Month) = 0, '>=' & YearStart(Today(), -1) & '<' & YearStart(Today(), 0), '>=' & YearStart(Max(Date)) & '<' & MonthStart(Max(Date), 1)))"}, Month, Year, YearMonth, Quarter, QuarterYear>} Budget_Sales)

[satish25]

No it isn't working

And even on selecting it's showing the current month value, not from Jan to the selected month

[sunny_talwar]

Let's take a step back... today is 01/17/2018... what would you want to see when nothing is selected? Now if you select 05/20/2017, what would you like to see

[satish25]

When nothing is selected, It should show me Sum(Budget_Sales) from 1/1/2017 to 12/31/2017

When I select 05/20/2017, it should show me Sum(Budget_Sales) from 1/1/2017 to 05/20/2017,

and when i select Month as May (No other selections), it should show me Sum(Budget_Sales) from 1/1/2017 to 05/31/2017