Aggregation on top of summarize function

[prabinnepak]

Hi All,

I have a dataset where i just need to calculate the max and min of a particular service data for a certain member and a claim 
and need to calculate an aggregate on top

Sample data 

CLAIM_HCC_ID	MEMBER_HCC_ID	SERVICE__DATE
2017649	741006200-02	1/10/2017
2017649	748002362-01	1/13/2017
2017660	748006897-01	1/23/2017
2017653	748012926-05	1/13/2017

Here there might be multiple service dates for a single member.

So for each member and the claim, i just wanted to know the minimum and the maximum service dates respectively.

Formulas :

MinDate = SUMX(SUMMARIZE('Reports vw_Health_Services_utilization','Reports vw_Health_Services_utilization'[CLAIM_HCC_ID],'Reports vw_Health_Services_utilization'[MEMBER_HCC_ID],"MIN",MIN('Reports vw_Health_Services_utilization'[MIN_SERVICE_START_DATE].[Date])),[MIN])

MaxDate = SUMX(SUMMARIZE('Reports vw_Health_Services_utilization','Reports vw_Health_Services_utilization'[CLAIM_HCC_ID],'Reports vw_Health_Services_utilization'[MEMBER_HCC_ID],"Max",MAX('Reports vw_Health_Services_utilization'[MAX_SERVICE_END_DATE].[Date])),[MAX])

After this i am calculating the difference between both this dates and retrieving the days among this dates

A1= DATEDIFF([MinDate],[MaxDate],DAY)

I need to analyze this monthly 

On top of this i wanted an aggregation i.e Sum(A1) but i am getting errors "An invalid numeric representation of a date value was encountered "

Can anyone please provide me some feedback 

Prabin Nepak

[v-shex-msft]

Hi @prabinnepak,

According to your formula, I'm not so sure why you try to get sum of date value. Can you explain more about this?

In addition, if you want to get the grouped min/max date and diff between them, you can try to use below formula to create new table with above data.

edsummarized =
ADDCOLUMNS (
    SUMMARIZE (
        'Reports vw_Health_Services_utilization',
        'Reports vw_Health_Services_utilization'[CLAIM_HCC_ID],
        'Reports vw_Health_Services_utilization'[MEMBER_HCC_ID],
        "MIN", MIN ( 'Reports vw_Health_Services_utilization'[MIN_SERVICE_START_DATE].[Date] ),
        "MAX", MAX ( 'Reports vw_Health_Services_utilization'[MAX_SERVICE_END_DATE].[Date] )
    ),
    "Diff", DATEDIFF ( [MIN], [MAX], DAY )
)

Regards,

Xiaoxin Sheng

[prabinnepak]

Hi

Thanks for your feedback

After executing this formula I am getting the following error 

"The expression refers to multiple columns.Multiple columns cannot be converted to a scalar value "

Thanks and Regards

Prabin Nepak  

[v-shex-msft]

Hi @prabinnepak,

>>"The expression refers to multiple columns.Multiple columns cannot be converted to a scalar value "

Above formula is calculated table formula, you can't use this formula in calculated column/measure.

Regards,

Xiaoxin Sheng