cancel
Showing results for
Search instead for
Did you mean:

## Calculation of measure based on Prior Week dates

Ranger

Hello Gurus

We have an requirement where we have to calculate the previous week's (week start from Monday to Sunday) amount based on passed date (Default to current date).

For e.g.

 Table A (Calendar Table) Date Day 1/1/2023 Sunday 1/2/2023 Monday 1/3/2023 Tuesday 1/4/2023 Wednesday 1/5/2023 Thursday 1/6/2023 Friday 1/7/2023 Saturday 1/8/2023 Sunday 1/9/2023 Monday 1/10/2023 Tuesday 1/11/2023 Wednesday 1/12/2023 Thursday 1/13/2023 Friday 1/14/2023 Saturday 1/15/2023 Sunday

 Table B (Transaction Table) Date Amount 1/2/2023 \$100 1/4/2023 \$150 1/6/2023 \$200 1/8/2023 \$400 1/10/2023 \$300 1/11/2023 \$250 1/12/2023 \$500 1/13/2023 \$600

Assuming, I am running the report as of date 1/14/2023 (Saturday), the required out put is :

 Date Amount 1/14/2023 \$850

Running as of date 01/17/2023(Tuesday), the required output is :

 Date Amount 1/17/2023 \$1,650

any pointers for this will be appreciated.

Regards,

Mritunjay

3 REPLIES 3
Partner

Hello Mritunjay,

I was able to use your sample data to create a dashboard that returns the expected results. My solution uses the selected date as a Presentation Variable on the dashboard, which is in turn referenced in Applied Filters used to filter the results of the transactions table.

I created a new dashboard and went to Manage Dashboard Filters. I created a new Presentation Variable with the following properties:

Then I created an Applied Filter that uses a formula column to determine which date was last Monday based on what was selected in the variable. Incorta assumes the first day of the week is Sunday, so I have to subtract six days from the weekStartDate() value to get the date of the prior Monday. I include the greater than or equal to operator so this is used as the start of the date range.

Next I create another Applied Filter that will filter on last Sunday. As I mentioned previously, the weekStartDate() formula will return the value of Sunday. Since this is the upper limit of the date range we want to use, I include the less than or equal to operator.

Now that my variable and filters have been setup, I can create the insights that return the values I want to see. I have an aggregated table that sums up the values to return the value you want, a detailed listing table with the individual transactions, and a table that shows the last Monday and Sunday values from the formula. Here are the screenshots for your two date examples.

I hope that helps solve your problem!

Ranger

Thanks @mrossPM2 , It worked perfectly👏

Community Manager

Great solution @mrossPM2 !