CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Brad
Brad

Reputation: 87

Is there an easy way to create a column with a repeated targeted value in Power bi

I have a dataset that I want to index to 100. I am trying to create a column that has the first value from the from each category, see "column" in image. I can't figure out how to do this dynamically, so that when the dataset updates it will index the next month to 100. i.e. currently January 2018 is = 100, next month February 2018 will be = 100

I have tried add "column from example" which works for the first month, but when its updated it loses the index, because its the actual value from that specific month, instead of being the earliest date value from each category.

The expected result is the values in the "column" columnenter image description here

Upvotes: 0

Views: 80

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You just need to look up the value associated with the minimum date for that category.

column =
VAR MinDate =
    CALCULATE (
        MIN ( TableName[date] ),
        ALLEXCEPT ( TableName, TableName[category] )
    )
RETURN
    CALCULATE (
        MAX ( TableName[value] ),
        ALLEXCEPT ( TableName, TableName[category] ),
        TableName[date] = MinDate
    )

This calculates the MinDate for the category in the current row and then looks up the value for date.

Upvotes: 1

Related Questions