Help with a measure using dates

[vickyprudhvi]

Hi

I have 3 tables

Date                                         ClaimRoot                         WCCSnapshot

DateID                                        ClaimID                               WCCID

CalendarDate                            ReportedDateID                     ClaimID

                                                  ClaimNumber                        AsofDate

                                                   ClaimAmt

I am planning to write a measure which takes in date from asofDate and calculate whole ClaimAmt for the reportedyear

For example

ASofDate 04/31/2016 then ClaimAmt should be calculated for ReportedDate 04/31/2015 to 04/31/206.

I wrote a measure as below

CALCULATE(      Sum( [ClaimAMT)FILTER(Date,
Date[CalendarDate]>=Max(WCCSnapshot[AsofDateStart])&& Date<=Max(WCSnapshot[AsofDate])))

I wrote a calculated colm in WCCSnapshot for previous year date AsofDateStart

this measure give me error saying . The Expression refers to multiple columns. Multiple columns cannot be converted into scalar value

Please help me with this

Thank you all

[v-caliao-msft]

Hi Vicky,

According to your description, you need to calculated the total ClaimAmt for ReportDate from to previous year to the AsofDate, right?

If that is the case, please add a calculated column in Date table
PreviousYear = DATEADD('Date'[CalendarDate],-1,YEAR)

Then add a calculated column in WCCSnapshot table
PreviousYearDate = LOOKUPVALUE('Date'[PreviousYear],'Date'[CalendarDate],WCCSnapshot[AsofDate])

Then you can calcualte total ClaimAmt use the DAX expression below.
= CALCULATE(SUM(ClaimRoot[ClaimAmt]),FILTER(ClaimRoot,ClaimRoot[ReportDateID]>=LOOKUPVALUE(WCCSnapshot[PreviousYearDate],WCCSnapshot[ClaimID],ClaimRoot[ClaimID])&&ClaimRoot[ReportDateID]<=LOOKUPVALUE(WCCSnapshot[AsofDate],WCCSnapshot[ClaimID],ClaimRoot[ClaimID])))

Regards,

[Greg_Deckler]

I haven't wired this up yet, but you have a syntax error in your formula below, there should be a comma "," in front of FILTER.

[vickyprudhvi]

@austinsense - Pls help