Error in PowerBI Deskopt using LastDate function

[vickyprudhvi]

Hi Friends,

I am using lastdate function in my tabular model.

Following is my measure

SUMX(VALUES(ClaimRoot[Claim Number]),
                                           CALCULATE(  SUM(ClaimRoot[ThreePointDaysQuantity]),LASTDATE(ClaimRoot[EffectiveTimestamp]))
                                          )

In PowerBi Dektop I am getting following error

A date Column containing duplicate dates was specified in the call to function 'LastDate'. This is not supported

Please help

[v-sihou-msft]

Hi @vickyprudhvi,

In DAX, when adding filters in CALCULATE() function, it must be either a boolean expression or a table expression that defines a filter. And the LASTDATE() returns a table containing a single column and single row with a date value. So the syntax of your DAX is correct. In this scenario, you need to check if there are duplicated rows in Cl​aimRoot[EffectiveTimestamp] column as the error message mentioned.

Regards,

[vickyprudhvi]

@austinsense - Pls help

[Sean]

@vickyprudhvi Tell us more about the table you are working on. The formula by iteslf seems okay

[austinsense]

Also it would help if you write out in a sentence what you want the measure to calculate. I's like DAX poetry, bonus point for a haiku ...

every claim number,

sum "three points day quantity",

last day in filter

I don't think you can throw a function like that into the second argument of a calculate.  you might need this ...

SUMX(VALUES(ClaimRoot[Claim Number]),
     CALCULATE(  SUM(ClaimRoot[ThreePointDaysQuantity]),
          FILTER(ClaimRoot,
               Cl​aimRoot[EffectiveTimestamp] = LASTDATE(Cl​aimRoot[EffectiveTimestamp]
	  )
     )
)