Sum for the Latest Month across the Years

[ghaliabed]

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

[lorenzoconforti]

can you re-share the file?

[ghaliabed]

please find it attached 

[lorenzoconforti]

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

[ghaliabed]

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.

[lorenzoconforti]

Totals are just another level of dimensionality (0)

See attached

[ghaliabed]

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

[lorenzoconforti]

Correct. I can't see a way of making it a master measure

[ghaliabed]

Hello

I am trying a new approach now and wanted to see if can help with the last part.

Assumptions:

Have Pivot table with:

1. Year --> Quarter --> Month in the columns 
2. 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.

[lorenzoconforti]

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

[ghaliabed]

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.