Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Lookupvalue using dates?

Hello,  I am trying to divide a number in Table 1 by a number with the corresponding date in Table 2 to get a percentage.   Table 1 Date Decimal 3/2/2019 0.9 3/2/2019 0.05 3/16/2...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    Iamnvt wrote:
    It looks like you are writing a Measure, not calculated column. I think you should turn off the date hierarchy to have [.Month] off.

    You can go to Model tab, and insert calculated column with the said formula. Check my PBI file for more details.

    Just want to close the loop on the question and address the date hierarchy. 

    The date hierarchy has been off and still the .[Month] etc. comes up. 

    I ended up using:

    Measure = Divide(sum('Table1'[Decimal]),calculate(sum('Table2'[Number])),0)
    which seems to work. TY
Anonymous's avatar
Anonymous
Not applicable
7 years ago
Iamnvt wrote:

hi,

 

you can use calculated column:

Calculated Column % = DIVIDE(Table1[Decimal], LOOKUPVALUE(Table2[Number],Table2[Date],Table1[Date]))

here is the PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk6pa5KcbigH1gCmIYg

 

I took it back a step and tried just getting the "Lookupvalue" function to work. 

Each time when I get to "Table2[Date] I get options for ".Date" ".Month" etc and after I chose one there is no option to pick "Table1[Date]" as my third attribute.

 

If I manually type over or copy and paste what you have above, I get this error:

"A single value for column 'Date' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

Please let me know if you have any ideas on the error? TY.

 

Iamnvt's avatar
Iamnvt
Icon for Continued Contributor rankContinued Contributor
7 years ago
It looks like you are writing a Measure, not calculated column. I think you should turn off the date hierarchy to have [.Month] off.

You can go to Model tab, and insert calculated column with the said formula. Check my PBI file for more details.
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Iamnvt wrote:
    It looks like you are writing a Measure, not calculated column. I think you should turn off the date hierarchy to have [.Month] off.

    You can go to Model tab, and insert calculated column with the said formula. Check my PBI file for more details.

    Just want to close the loop on the question and address the date hierarchy. 

    The date hierarchy has been off and still the .[Month] etc. comes up. 

    I ended up using:

    Measure = Divide(sum('Table1'[Decimal]),calculate(sum('Table2'[Number])),0)
    which seems to work. TY