Thanbk you
I currently have a dashboard on an app that shows a metric over a trended period of time in a pivot table. The user selects date from the master item dimension called “Data As Of Date” and the number of months that they would like to look back. In my pivot, my columns are Data As Of Date and my measure is shown in the formula below where [Value] is the metric I am trying to show over time. So if the user selects “Data As of Date” = 05/31/2018 and a window of 12 months then the pivot table will only show 06/30/2017 to 05/31/2018.
Measure in Pivot
=Only ({<[Data As Of Date]={">=$(=AddMonths(vDynamicMaxDate,-vMonthWindow+1)) <=$(=MonthEnd(vDynamicMaxDate))"}> } [Value])
Variable | Formula |
vDynamicMaxDate | =Max(AsOfDate) |
vMonthWindow | 12 |
This formula works in my current app which is on version 3.2 but I am upgrading to the April 2018 version and it appears to have some issues. When I try it on the new version, it just shows the 1 month that the user selected for Data As Of Date (and not the entire window).
Can I not reference “Data As Of Date” in this set analysis expression because it is a Master Item? The Master Item formula is Data As Of Date = Date(AsOfDate,’MM/DD/YYYY’). AsOfDate is a date field that is in a table with the field [Value] that I am trying to show in the pivot. I found that in the new version, the chart works if I select a value for AsOfDate (not Data As Of Date) and I change the formula to the following:
=Only ({<[AsOfDate]={">=$(=AddMonths(vDynamicMaxDate,-vMonthWindow+1)) <=$(=MonthEnd(vDynamicMaxDate))"}> } [Value])
I really want the users to be able to use the “Data As Of Date” dimension instead as that is the field that they use across the entire app and AsOfDate shows ‘M/DD/YYYY’ instead of ‘MM/DD/YYYY’.
Thank you,
Brian
