Calculation of measure based on Prior Week dates

[msinha8]

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

[mrossPM2 · Accepted solution]

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!

[msinha8]

Thanks @mrossPM2 , It worked perfectly👏

[JoeM]

Great solution @mrossPM2 !