Forum Discussion

vickyprudhvi's avatar
vickyprudhvi
Helper IV
10 years ago

Error in PowerBI Deskopt using LastDate function

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

 

Need Help

4 Replies

    • Sean's avatar
      Sean
      Community Champion
      10 years ago

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

      • austinsense's avatar
        austinsense
        Impactful Individual
        10 years ago

        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]
        	  )
             )
        )
  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    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,