cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apowerbiguy
New Member

Calculation with LookupValue to determine if greater than previous period value

Good Afternoon Everybody,

 

I am hoping that you can assist with a calculation error I am having. I am attempting to create a new column which will show 

"↑","↓" depending on whether the current value is higher or lower than the previous value.  This table has multiple items in one table and it would need to look up the previous value while ensuring that the BU field and the FLEET column values are the same. I do have a date table which is called Calendar.
 
Does anybody have any ideas on how to do this? I've done this in other tables but never where multiple items are in the same table.
 
Thank you for the help in advance!
 

 

 

powerbi question.png

 

 

 

1 ACCEPTED SOLUTION
selimovd
Community Champion
Community Champion

Hello @apowerbiguy ,

 

I'm not sure if I fully understood the requirements. I am checking for the value from the last quarter with identical BU and identical Fleet. Is that right?
I am just assuming the table is called MyTable. Try the following approach:

Previous =
VAR rowBU       = MyTable[Bu]
VAR rowFleet    = MyTable[Fleet]
VAR lastQuarter = DATEADD( MyTable[Date], -1, QUARTER )
VAR FilteredTable =
    FILTER(
        MyTable,
          MyTable[Bu] = rowBU
          && MyTable[Fleet] = rowFleet
          && MyTable[Date] = lastQuarter
    )
VAR AvailabilitytPQ = SUMX( FilteredTable, MyTable[Availability] )
RETURN
    IF( MyTable[Availability] >= AvailabilitytPQ, "↑", "↓" )

 

If you need any help please let me know.
If I answered your question I would be happy if you could give it a thumbs up 👍!

Best regards
Denis

View solution in original post

3 REPLIES 3
selimovd
Community Champion
Community Champion

Hey @apowerbiguy ,

 

sure, VAR is just a variable and the result is always saved in the filtercontext when the variable is set. This I used to get the current context. The following video is explaining that:
https://www.youtube.com/watch?v=71ERlpfhmwU


Also check the article from SQLBI:

https://www.sqlbi.com/articles/variables-in-dax/

 

Best regards
Denis

selimovd
Community Champion
Community Champion

Hello @apowerbiguy ,

 

I'm not sure if I fully understood the requirements. I am checking for the value from the last quarter with identical BU and identical Fleet. Is that right?
I am just assuming the table is called MyTable. Try the following approach:

Previous =
VAR rowBU       = MyTable[Bu]
VAR rowFleet    = MyTable[Fleet]
VAR lastQuarter = DATEADD( MyTable[Date], -1, QUARTER )
VAR FilteredTable =
    FILTER(
        MyTable,
          MyTable[Bu] = rowBU
          && MyTable[Fleet] = rowFleet
          && MyTable[Date] = lastQuarter
    )
VAR AvailabilitytPQ = SUMX( FilteredTable, MyTable[Availability] )
RETURN
    IF( MyTable[Availability] >= AvailabilitytPQ, "↑", "↓" )

 

If you need any help please let me know.
If I answered your question I would be happy if you could give it a thumbs up 👍!

Best regards
Denis

View solution in original post

Thank you! This is perfect and worked, I am looking to learn more about VAr do you have any recommendations for where to learn more?

 

Thank you again for the help!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors