cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

## Column output based on another column value and time function

I have a table like that:

I'm comparing values from CurrentMonth vs PreviousMonth (based on the month selected in the slicer) for all of the KPIs, and based on that, I assign a specific result.

However, for each KPI, there is a logic behind when comparing. The logic is:

So, for KPI A, the CurrentMonth value must be 10% higher then PreviousMonth. When this is true, then assign 1. If not true, then 0. For KPI B, it has to be 3% higher. And for KPI C, it just need to be higher.

To pull the CurrentMonth and PreviousMonth values, I created two measures, one of them with a time function:

`CurrentMonth = SUM( [KPIValue] )`
```PreviousMonth = CALCULATE (SUM( [KPIValue] );
PREVIOUSMONTH( [Date] )
)```

I tried to create a Column in my table to assign the output (Result), and used a Switch function like this:

```Result = SWITCH (
[KPIName];
"KPI A"; IF( [CurrentMonth] >= [PreviousMonth]*1.10;1;0 );
"KPI B"; IF( [CurrentMonth] >= [PreviousMonth]*1.03;1;0 );
"KPI C"; IF( [CurrentMonth] >= [PreviousMonth];1;0 );
)```

My issue is that when I try to use this Switch, it does not work, since the PreviousMonth come as blank (it can not calculate a time function for each row of the table - only works as a measure).

I'm now lost how should I proceed from here. I tried to create a Measure instead of a Column, but then the Switch function does not accept the KPIName as parameter.

Any ideas? Hope I've been clear here, if not, please let me know what further information you need.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Column output based on another column value and time function

@Ale Please try this as a "Measure"

```Result = SWITCH(SELECTEDVALUE(Test155[KPIName]),
"A",IF(_KeyMeasures[Test155CurrMonth]>=_KeyMeasures[Test155PrevMonth]*1.10,1,0),
"B",IF(_KeyMeasures[Test155CurrMonth]>=_KeyMeasures[Test155PrevMonth]*1.03,1,0),
"C",IF(_KeyMeasures[Test155CurrMonth]>=_KeyMeasures[Test155PrevMonth],1,0),
0)```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

2 REPLIES 2
Super User

## Re: Column output based on another column value and time function

@Ale Please try this as a "Measure"

```Result = SWITCH(SELECTEDVALUE(Test155[KPIName]),
"A",IF(_KeyMeasures[Test155CurrMonth]>=_KeyMeasures[Test155PrevMonth]*1.10,1,0),
"B",IF(_KeyMeasures[Test155CurrMonth]>=_KeyMeasures[Test155PrevMonth]*1.03,1,0),
"C",IF(_KeyMeasures[Test155CurrMonth]>=_KeyMeasures[Test155PrevMonth],1,0),
0)```

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Highlighted
Regular Visitor

## Re: Column output based on another column value and time function

Uow, I was missing the SELECTEDVALUE thing. I never heard about that (I started with PowerBI last week).

Apparently it worked! I also found a second solution but it envolves creating multiple columns on the dataset (previousmonth column, previousmonthvalue column, and from there the result column). It's not that elegant tho. I will stick with your solution. Thank you so much!

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 906 guests
Recent signins:
Please welcome our newest community members: