Forum Discussion

VModani's avatar
VModani
Frequent Visitor
2 years ago
Solved

Measure not working on calculated date column

My date column [ptb_date] is in Unix timestamp, hence I have to add a column and convert it to DateTime in Power Query M.

 

Add Calculated Column: 

= Table.AddColumn(sahay_deals_View, "DisbDate", each #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, -6, 0, [ptb_date]))

Then I convert this to Date/Time

 

Now I create a measure that calculates the difference between NOW() and above DisbDate

Measure = Calculate(DATEDIFF(Table[DisbDate],NOW(),DAY))

 

I need to know the difference for each row, but this measure does not work. The error returned is that the column has many values without specifying any aggregation. But I don't need to aggregate.

 

I had made the same measure 2 years ago, but copy-pasting from that pbix is not working.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi VModani ,

     

    I made the same attempt by creating a new column in the power query, converting the timestamp to Date/Time and then creating a measure based on this new column via dax. reference below:

    = Table.AddColumn(#"Removed Columns", "Custom", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,Number.From([ptb_date])))

    now_days = if(HASONEVALUE('Table'[ptb_date]),NOW(),BLANK())

DifferenceInDays =
IF (
    HASONEVALUE ( 'Table'[ptb_date] ),
    DATEDIFF ( MAX ( 'Table'[Date/Time] ), NOW (), DAY ),
    BLANK ()
)

     

    Best Regards,
    Adamk Kong

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi VModani ,

     

    I made the same attempt by creating a new column in the power query, converting the timestamp to Date/Time and then creating a measure based on this new column via dax. reference below:

    = Table.AddColumn(#"Removed Columns", "Custom", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0,Number.From([ptb_date])))

    now_days = if(HASONEVALUE('Table'[ptb_date]),NOW(),BLANK())

DifferenceInDays =
IF (
    HASONEVALUE ( 'Table'[ptb_date] ),
    DATEDIFF ( MAX ( 'Table'[Date/Time] ), NOW (), DAY ),
    BLANK ()
)

     

    Best Regards,
    Adamk Kong

     

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

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    VModani,

     

    Even if you don't need to aggregate, you still need to wrap your table[column] reference in an aggregate function or a function that returns one value like SELECTEDVALUE or VALUES (depending on the context). A common technique is to use MAX.