Monthly Subtotals Not Adding Up

[Grasshopper]

Hi All,

I'm having some difficulty understanding how I need to adapt the DAX measure to make the monthly subtotal equal to the visual sum of matrix rows. Essentially, I want to calculate the date difference between the date a product was installed (in a particular month) vs. the end of month date. I can't quite get my head around what filter context considerations are needed to make the monthly subtotal add up.

In terms of the granularity, this is needed at the machine level - hence, using the machine serial number from a Dim table containing a complete list of all products/machines vs. the respective machine serial number. However, the measure also needs to work on a date level - where the monthly subtotal should be the aggregated sum of machines time-in-service for the month of installation.

Each row in the transaction table is unique - corresponding to the unique machine serial number - which is the reason the Min function is used to extract the machine install date.

As can be seen from the measure, there are 3x tables being referenced:

1. A dim table containing the machine serial number (connected to in a number of fact tables). The connection from the dim table to the VMAX_BASE_INSTALLED_PRODUCT_OBJECT fact table is via the uniqu machine serial number
2. A dim date reference table containing a list of dates from (again connected to a number of fact tables). Connection to the VMAX_BASE_INSTALLED_PRODUCT_OBJECT fact table is made to the Installation Date field.
3. A fact table (SVMAX_BASE_INSTALLED_PRODUCT_OBJECT) containing the transactional data.

Here's the measure written thus far:

POPULATION_OPERATIONAL_TIME_(MONTH) =

Var LastCurrentDate = MAX(DIM_CALENDAR_TABLE[REFERENCE_DATE])

Var InstallDate = MIN(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C])

RETURN

SUMX(

DISTINCT(DIM_SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]),

CALCULATE(DATEDIFF(InstallDate, lastCurrentDate, DAY))

)

Below is a snapshot of the matrix visual displaying the measure 'POPULATION_OPERATIONAL_TIME_(MONTH)' which aims to calculate the machine time in service for the month of installation. The monthly total for Jan 2017 is shown as 2175. The correct total (the sum of matrix rows) = 1361 days. I'm not even sure how the figure of 2175 is being derived...

I'm not able to upload the PBI file as this consists of business confidental data and therefore, I'm very much hoping that a solution can be proposed based on the description of the issue alone.

Any suggestions on solution and an explanation as to what I'm missing would be hugely appreciated.

Please let me know if additional information/explanation is needed to support a solution.

[Grasshopper · Accepted solution]

Aftrer writing an update, it occurred to me that the issue I'm seeing (given the apparent use of the same row value in the date diff aggregation operation) that the issue was more about eval context and the probable incorrect use of variables in the measure. I changed the measure to the following (ensuring the installation date reference/value was now directly used in the measure), which has resolved the issue and the measure now works at the month/year date levek as well as the machine level. 

VAR Result =

SUMX(VALUES(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__SERIAL_LOT_NUMBER__C]),

CALCULATE(

DATEDIFF(VALUES(SVMAX_BASE_INSTALLED_PRODUCT_OBJECT[SVMXC__DATE_INSTALLED__C]), LastCurrentDate, DAY)

)

)

[Grasshopper]

Hi Polly,

Apologies for the tardy response! It's been a somewhat hectic week and this is the first opportunity to respond back.

Thank you for the suggestion. I cannot get this to work though and receive the same monthly total as before. I had attempted to use Summarize as a means to group and associate the aggregation measure prior to your suggestion and couldn't manage to make the measure work at the date level. 

I guess the filter context from the matrix table at the month/year level results in the situation where the aggregation is not a sum of calculated values (the date diff) associated at the row/machine ID level, but uses a value (which looks to be the same value) corresponding to each of the rows active/in context for the given month. For example, the countrows values for Jan 2017 = 75 (75x machines having been installed during the month of Jan). The current meaure shows an aggregated value of 2175 for Jan 2017, which seems to suggest that the same whole number (a value of 29 in this case) is being used 29x75 =  2175. For other month/year totals, devision of the month/year total by the machine/row count results in a whole number value. Again, this suggests that the same value is being used for each row, (albeit that this value is different for the different months in the year). Again, if I take a specific month reference (March 2017 in this case) which shows a month/year total of 4320 and 144x installed machines/rows, then the row aggregator value would appear to be 4320/144 = 30.   So even when using the summarized table to associate machine level date delta values at either the machine or date level the result is the same. Well, I'm sufficiently confused...

Hope the explanation makes sense.  

[v-rongtiep-msft]

Hi @Grasshopper ,

It seems the measure total value is error.

Please create another measure.

_contract_ = var _a = [POPULATION_OPERATIONAL_TIME_(MONTH)]
var _b = SUMMARIZE('table','table'[date],"aaa",[POPULATION_OPERATIONAL_TIME_(MONTH)])
return
IF(HASONEVALUE('table'[date]),_a,SUMX(_b,[aaa]))

Or you can use the column name in the red circle in the following image instead of the date column.

How to Get Your Question Answered Quickly  

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.