Need WTD, MTD and YTD columns on one table

[Rich5678]

Hi,

I have a data table with following pattern:

Date       Order Quantity

1/1/22      100

1/3/22      200

1/15/22    500

1/29/22    700

1/31/22    900

2/5/22       400

2/8/22       300

2/12/22     400

2/23/22    350

2/25/22     950

All I am looking for, is a simple Table, where I can show WTD, MTD and YTD Order Quantity as different columns on the Same Table, based on "Date" that I select on a filter pane.

For example, when I select 2/25/22 on Date (filter panel), I should see below

WTD            MTD                YTD

1,300          2,400            4,800

I saw some very confusing responses for this type of question, in the past.

A simple direct response is much appreciated.

Thanks

[vinieme12 · Accepted solution]

As below;

WTD

=Sum({<Datefield={">=$(=WEEKStart(Max(Datefield)))<=$(=Date(Max(Datefield)))"}>}Measure)

MTD

=Sum({<Datefield={">=$(=MONTHStart(Max(Datefield)))<=$(=Date(Max(Datefield)))"}>}Measure)

YTD

=Sum({<Datefield={">=$(=YEARStart(Max(Datefield)))<=$(=Date(Max(Datefield)))"}>}Measure)