cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation of measure based on Prior Week dates

msinha8
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)
DateDay
1/1/2023Sunday
1/2/2023Monday
1/3/2023Tuesday
1/4/2023Wednesday
1/5/2023Thursday
1/6/2023Friday
1/7/2023Saturday
1/8/2023Sunday
1/9/2023Monday
1/10/2023Tuesday
1/11/2023Wednesday
1/12/2023Thursday
1/13/2023Friday
1/14/2023Saturday
1/15/2023

Sunday

 

 

Table B (Transaction Table)
DateAmount
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 :

DateAmount
1/14/2023$850

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

DateAmount
1/17/2023$1,650

any pointers for this will be appreciated.

 

Regards,

Mritunjay

3 REPLIES 3

mrossPM2
Partner
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:

datevariable.png

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.

lastmonday.png

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.

lastsunday.png

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.

jan14.pngjan17.png

I hope that helps solve your problem!

msinha8
Ranger

Thanks @mrossPM2 , It worked perfectly👏

JoeM
Community Manager
Community Manager

Great solution @mrossPM2 !