How to change Today() to a date you can select

[GeorgeEard]

I am trying to find out the amount of orders there were at a certain point in time

I have a dimension:

=If([Orders Order Date] < Today(), '1',

If([Contract Transfer Date] > today(), '1',

If([Contract Transfer Date] >= '-', '1', '0')))

I am currently using this which works but only for today. How could i change this so that any date in time could be selected and we could see how many orders we had at that time?

Thank you

[PrashantSangle]

you can store your selected date in variable & use that variable instead of Today

[F_B]

Hi @GeorgeEard , welcome to the community!

You can create a variable in the load editor and set it as today by default:

LET vDate=Today();

In your dimension substitute Today() with the variable:

If([Orders Order Date] < $(vDate), '1',

If([Contract Transfer Date] > $(vDate), '1',

If([Contract Transfer Date] >= '-', '1', '0')))

With a variable input in your sheet you can then choose a different date.

[GeorgeEard]

Thank you for your reply,

How would i do that?

[GeorgeEard]

Could I use the date picker to influence this variable?

[PrashantSangle]

yes you can. considering your date field name is cal_date

then try below

If([Orders Order Date] < date(max(cal_date)), '1',

If([Contract Transfer Date] > date(max(cal_date)), '1',

If([Contract Transfer Date] >= '-', '1', '0')))

check you date format while comparing or convert them in num then use it.

Regards,

Prashant Sangle