YTD ane previous month based on selected month

[fredericlehner]

Hi,

My script :

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -1)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -1), 1, 0) As 'P1M',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -13)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -13), 1, 0) As 'P1M-1',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -3)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -1), 1, 0) As 'P3M',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -15)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -13), 1, 0) As 'P3M-1',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -12)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -1),1, 0) As 'P12M',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= MonthStart(Today(), -24)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), -13),1, 0) As 'P12M-1',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= YearStart(Today(), 0)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= MonthEnd(Today(), 0), 1, 0) As 'YTD',

if(Date(Date#([Completed Date], 'DD.MM.YYYY')) >= YearStart(Today(), -1)
and Date(Date#([Completed Date], 'DD.MM.YYYY')) <= AddYears(Today(), -1), 1, 0) As 'YTD-1',

---------------------------------------------------------------------------------------------------------------------------

I would like the user to be able to choose the end date (reference date).

Example: choose 13 march 2019 -> P1M = February 2019 and P1M-1 = February 2018. etc. for PM3, P3M-1, ..., YTD, YTD-1.

What to change in the script?

Thank very much for your help.

Fred

[Ezir]

Hi @fredericlehner ,

As the user can choose the reference date, it is more appropriate to work with variable in the dashboard.

In the script you would just load [Completed Date] and in the dashboard create variables such as "P0", "P1M", "P1M-1" etc ...

In your panel, I suggest the extension Date picker or Variable input

I hope helps.

[fredericlehner]

Thanks Ezir !

In my next version, I will implement your proposal.

But, any solution exist with my situation ?

Thank you for your help !

Fred