Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
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.
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).
After that i've tried to create the measure below but it's not working:
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
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
Then you will want to add a proper Calendar table to your data model and fix your data model relationships.
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.
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |