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).
|Table A (Calendar Table)|
|Table B (Transaction Table)|
Assuming, I am running the report as of date 1/14/2023 (Saturday), the required out put is :
Running as of date 01/17/2023(Tuesday), the required output is :
any pointers for this will be appreciated.
Solved! Go to Solution.
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!