Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Relannes
Frequent Visitor

How can I find the difference between two filtered values?

Hello! I'm from Brazil, so I'm sorry if some images are in portuguese or if my english is bad (and I'm also new to Power BI so be patient with me hehe).

 

But basically i need to calculate the difference between two values, but each of them are going to be switching according selection on an external filter. To better understand my issue:

 

1) My data base is a bunch of Index columns (1201,1202,1203...) with different values in each line that correspond to a date. So on March/2013, the index 1201 was 100,524. On April/2013 was 101,485, and so on.

Relannes_2-1663980802380.png

 

2) I'd like to create some sort of calculator, so Power BI could show me the difference between values of an index on different dates. For example:

  1. The index 1201 value on march/2013 = 100,524
  2. The index 1201 value on march/2014 = 113,289
  3. The difference = 12,765

The thing is that the date is going to be changing at all times through an external filter, so the subtraction has to change accordingly.

 

HOW I'VE TRIED TO DO ( and failed D; )

At first I've created two measures to return the values already being effected by the filters. For example, the measure "Index 1201 according Data Filter" returns to me the index 1201 value according to the selected date, in this case, april of 2013. I used CALCULATE and ALLSELECTED to do that.

Relannes_6-1663983147588.png

 

The other measure is basically the same, but it's another filter that also corresponds to a date but through a Project Number (i guess the data base in this case it's not relevant).

Relannes_7-1663983283692.png

 

After that i've tried to create the measure below but it's not working:

Relannes_5-1663982792623.png

 

Could you guys help me? Is it possible to do what I am trying to do on Power BI? There's a better way to do that? 

 

https://drive.google.com/file/d/1c5DpEI4rsoPX8U8EBepHJ9gSsSmckLmg/view?usp=sharing

 

 

 

6 REPLIES 6
lbendlin
Super User
Super User

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

You may want to consider unpivoting your source data (Index History table) to make it more palatable for Power BI

 

lbendlin_0-1664144425395.png

 

Then you will want to add a proper Calendar table to your data model and fix your data model relationships.

lbendlin_0-1664145028303.png

 

Hello! Thank you for your reply and i appreciate the tips. The calendar table is something very useful and I am most certainly going to adhere.

 

Unpivotting the Index History Table surely will help make Power BI data more "lighter", but since I need to do a subtraction between values of each index according to two different date filters (both external filters), i think it wouldn't be possible to do that by creating only one column with all the indexes, right? I could be wrong, so if you have any ideas of how i could do this please let me know hehe

You can then use measures to calculate the differences.  Give it a try and see if that setup makes it easier for you.

Just did the calendar table and re-did the relationships. Tried unpivotting the table but since i need the value per each index in a card, i wasn't able to calculate the difference, so i kept the index structure.

Relannes_1-1664153407772.png

 

The cards change according the filters just fine, but even after this changes, the measure i've created to do the difference it's still not working. Do you have any idea why's that?

Relannes_0-1664153362509.png

 

Rewrite your measures to use the calendar date as needed.

Could you show me how would you rewrite the measures to appear the difference? Because i tried doing that, but it's still not working. Don't know if i'm doing something wrong.. take a look below

Relannes_0-1664229319438.png

Relannes_1-1664229368097.png

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors