
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum for the Latest Month across the Years
Hello
Goal:
- Create a Master Measure for Total Number of Employees to be used against any dimension
- Total Number of Employees is a cumulative value so it is not a direct Sum, ie: it should be the Sum of Employees found in the Latest Month.
Examples below on data and output chart needed.
Currently i tried the below Set Expression:
SUM ({$< [Year Month]={$(=MAX([Year Month]))} >} [Number of Employees])
However, when i place this measure in a bar chart grouped by the Year, only the Latest Year will get a value while the rest will be ZERO.
Ex: if Latest Month in the data is = 2020-08, but knowing i have months in the previous years
Fiscal Year | Total Employee |
2019 | 0 |
2020 | 10 |
Example of Data Present:
Calendar:
Date | Year | Fiscal Year | Year Month |
2019-08-01 | 2019 | 2019 | 2019-08 |
2019-09-01 | 2019 | 2019 | 2019-09 |
2019-10-01 | 2019 | 2020 | 2019-10 |
Fact Table:
DateRegionHead Count
Date | Region | Number of Employees |
2019-08-01 | A | 1 |
2019-08-01 | B | 1 |
2019-09-01 | A | 2 |
2019-09-01 | B | 2 |
2019-09-01 | C | 2 |
2019-10-01 | A | 3 |
2019-10-01 | B | 3 |
2019-10-01 | C | 3 |
Target Measure/Chart:
Table Chart that will show this:
Fiscal Year | Total Employee | Logic |
2019 | 6 | Sum of the Regions found in Month 2019-09 ; the last month of the Fiscal Year 2019 |
2020 | 9 | Sum of the Regions found in Month 2019-10 ; the last month of the Fiscal Year 2020 |


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you re-share the file?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
please find it attached


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The calculation works as expected (I've added a table that shows this)
The issue is that you are using a pivot table; there are different behaviours when expanded/collapsed . You need to use two different formulas and pick using the Dimensionality() function
See attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes it works for this specific pivot table and chart combination.
But i guess there isn't a way to make a true Master Measure that can be applied regardless of chart composition.
Also one that allows u to show totals, as currently in pivot table totals stay null.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Totals are just another level of dimensionality (0)
See attached

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes this works for specific charts
but yes so far haven't found a way to make it a master measure. Available for self-service
only as prepackaged calculations


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Correct. I can't see a way of making it a master measure

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello
I am trying a new approach now and wanted to see if can help with the last part.
Assumptions:
Have Pivot table with:
- Year --> Quarter --> Month in the columns
- Any dimension in the Rows
Current measure being used:
if
(
Dimensionality() = 2
,sum(SignedData) // Month Level
,sum({<[Fiscal Month Number]={$(=max([Fiscal Month Number]))}>}SignedData)
)
If i make the pivot table to only have Quarter --> Month OR Year --> Month
It is giving me the results i want
Ex: (of course switch Dimensionality()=1)
Quarter --> Month
- Looking at Q.3 when expanded , the values at the Month Level for the Rows is correct but the Total is wrong
- Looking at Q.4 when not expanded, the values at the Rows is correct and the Total is correct
This is closer to what is needed as i can switch the Dimension present in the row and not be affected.
But as you can the roll up is still an issue for Totala, and also when i use all three Time levels (Year --> Q --> Month)
I get results like ones below:
Where only the Quarter and the Month that is at the max has values while the rest become ZERO.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you upload the dashboard?
Also, totals only work for Q4 because that's when you have your max([Fiscal Month Number]); when you use set analysis, you are looking for the max month across the current selection, not per row/column. You will need to use firstsortedvalue and aggr like we've done before

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please find dashboard here.
And yes i get that for the Max(Fiscal Month Number)
But isn't a set analysis where the value of the Max changes by the grouping at the chart level ?
Since max(fiscal Month Number) at Q1 level is different then Q2, but seems value in the set analysis only gets calculated before the dashboard is rendered.
