Please find attached workbook with YTD values.
I have done following modifications in your workbook:
1) Click on the drop down button of SUM(Sales) and you will see "Quick Table Calculation" and then click on "Running Total".
Since it is a window calculation, you need specify the way it should be computed. In this case, you need to use "Table (Down)" as shown below:
2) Then create a calculated field "Rank" with the following calculation:
This assigns the rank for each and every value in the window.
Drag "Rank" field into filter shelf and specify range 1 to 1 (Essentially selecting the latest record) and click on "Apply"
Specify "Table (Down)" computation for "Rank" field as shown below:
This will display the latest month data with YTD value.
Hope this helps.
Book3.twbx 10.5 KB
Thanks for the help, now i got stuck again after adding a new dimension.
Let's say now i want to only see the categories for month of May based on the same parameters selection, and then show the YTD sales.
Is that possible?
Book3.twbx 18.0 KB
Please find attached workbook with the ask.
The trick to get it is the way we compute the window calculations like running total & rank_unique.
Click on Sum(Sales) drop down and then click on "Edit Table Calculation"
The following window will pop up. Click on Running along drop down and select "Advanced" option.
Once you click on advanced option the following window will pop up. Partitioning refer to the fields on which you want to do your partition and remaining fields should go to addressing side. In this case since the running sum should be calculated at each category level, we keep category on partitioning side and the rest of two fields are moved to addressing side.
In the similar manner, you need to change the way "Rank" has been computed and then you will see following window:
Finally filter the window using Rank = 1
Book3.twbx 14.1 KB