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.
Hi there,
I hope you all are doing good.
I have a data connected via direct query.
There are around 36 tables, but we will focus only on 2 of them.
One is period table which contains the field "Period (L2)".
Second is Capital table which contains the field "Amount".
Now I wanted a difference between amount of one period to another, so for example to calculate the values difference from the period FA 21 and FA 20, I've used the following measure andit worked perfectly.
Now this is all convenient for me but not for other people I share the file with.
What I want is maybe a slicer for both First and second period, which will directly refer the period filter in measure.
For example: In place of
'Period'[Period (L2)] IN { "FA 2020"}
we could select a period from the slicer and then it would appear in place of FA 2020, like:
'Period'[Period (L2)] IN {slicer value}
That will definitely help and it will make it more dynamic.
Is there a specific way to achieve this.
If you think I am going the wrong way and you have a specific solution for this, please feel free to share.
I will be very thankful to you all.
See if this thread helps (see the solution using slicers at the end)
https://community.powerbi.com/t5/Desktop/Waterfall-data-Value-Change-in-Matrix/td-p/2388951
Proud to be a Super User!
Paul on Linkedin.
Hi @PaulDBrown
I've thoroughly checked the solution you provided, I dont know how to explain it but I am unknown how the database administrators has created this data and on what basis, as the field I want to use is showing it as a measure, The calculator sign in front of the field. When I click on it to see the code, Nothing appears.
Maybe that is the reason in SUM COST A, the sum expression doesnt gets applied, neither in SUM COST B.
I tried it to do without using the SUM expression, but the matrix is coming up empty.
I dont know what is the issues.
Maybe because my data is little bit different.
I have live data with direct query added. Period is in different dimension and capital is in different dimension. The other 2 attributes which act as row value and header value are also from different different dimensions. REAL PAIN IS I CAN'T EVEN SHARE THE SAMPLE DATA.
IF you want to get more knowledge of my data, you can see the replies given to vojtechsima above.
I've tried to elaborate my problem in the best way possible.
Hi, @Birinder ,
you can incorporate "PREVIOUSYEAR" function and "MAX" function.
It should work like this:
In your Current year measure, you use MAX(Date) as the filter (this will take max Date value from your Slicer), for this, you need to have a Date column.
Then in your next measure, you will just use PREVIOUSYEAR as the filter for Date and it should take the previous Year from the Slicer filter context.
Hi @vojtechsima
Thank you so much for reply.
I have actually tried modifying the measure as you said.
But the results are not coming right.
It would be really cool and appreciable, if you can write me the code.
Just an FYI, I have already created 2 seperate columns for current and previous periods.
Hi, @Birinder ,
Sorry for the late reply, I tested the proposed thing but it didn'T work as I expected, so I created a measure for you that will deal with your situation, given you have Date Table:
CurrentYear Sales =
var LatestDateInCurrentYear = CALCULATE(MAXX('Date','Date'[Date]), ALLEXCEPT('Table','Date'[Date]))
var EarliestDateInCurrentYear = CALCULATE(MINX('Date','Date'[Date]), 'Date'[Year] = YEAR(LatestDateInCurrentYear))
var LatestDateInPreviousYear = CALCULATE(MAXX(FILTER('Date','Date'[Year] = YEAR(LatestDateInCurrentYear)-1),'Date'[Date] ), ALLEXCEPT('Table','Date'[Date]))
var EarliestDateInPreviousYear =CALCULATE(MINX('Date','Date'[Date]), 'Date'[Year] = YEAR(LatestDateInPreviousYear))
var _currentYear = CALCULATE(SUMX(FILTER('Table', 'Table'[Date] <= LatestDateInCurrentYear && 'Table'[Date] >= EarliestDateInCurrentYear), 'Table'[Amount]))
var _previousYear = CALCULATE(SUMX(FILTER('Table', 'Table'[Date] <= LatestDateInPreviousYear && 'Table'[Date] >= EarliestDateInPreviousYear), 'Table'[Amount]))
var __calc = _currentYear - _previousYear
return __calc
hi @vojtechsima
No issues for replying late and thanks a lot for answering.
I really appreciate it.
But one thing I am thinking of is that maybe you misunderstood the period column values.
The solution that you have referred to is for datetype date column.
I have a period column but it all have categorical values. There is no way I can put them as a slider slicer.
Like, see this picture (Ignore tables above the period cards). Here I want to reference these two categorical values AKA period values in my measure stated very above.
So that If I make selection of FA 2020 in left slicer and Q3 2019 in right one, it should give me the difference of values from FA 2020 and Q3 2019, The same way I am calculating in the code:
I am really sorry for all the mess, If you think that I am getting it wrong. Then please change my perspective. I really appreciate you for sticking along.
Hi, @Birinder
If you have separated columns for the periods, that should be simpler:
CurrentVsPrevious =
var Period_Current = SELECTEDVALUE(Period[Period_Current])
var Period_Previous = CALCULATE(SELECTEDVALUE(Period[Period_Previous]), ALL(Period[Period_Current]), USERELATIONSHIP('Table'[Period],Period[Period_Previous]))
var sum_current = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), 'Table'[Period] = Period_Current))
var sum_previous = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), 'Table'[Period] = Period_Previous))
var difference_calc = sum_current - sum_previous
return difference_calc
However, if you can't show sample data, try to re-create them with random values but keep the formatting as you would have in your table, just put it together in Excel and Paste it here, it will really help. Thank you
Hi @vojtechsima
Thanks for the reply. I've tried this solution, the table is coming blank. Is there anything I am missing. I want to confirm few things.
1. When we used USERELATIONSHIP function, what kind of relation do we need to establish, I created one to one dummy period table with original table.
2. Is there any need to edit interactions from format pane. I am using the default method.
And sorry, I cant upload files on Onedrive due to my organisation restrictions.
However I can give you brief explanation how things are in it.
* The data is connected live with direct query enabled.
* Each and every data table is related to each other.
* Amount column is in Capital table.
* Period column is in Period (D) table.
* I am using an attribute for column field as well which is named as "Layer" from "Analysis" table.
* There are 3 filters being applied on the page and they each are from different tables as well.
* I dont know how to explain it but I am unknown how the database administrators has created this data and on what basis, as the Amount field that I want to use, is showing as a measure, The calculator sign in front of the field. When I click on it to see the code, Nothing appears.
Thank you so much for your constant help. See if there is anything you can do.
Hi, @Birinder ,
The relationship is like this:
And for the sample data, you can just paste here, you know like this for example:
DateAmountPeriod
01.01.2021 | 50 | ALL2021 |
31.01.2021 | 50 | ALL2021 |
10.06.2020 | 10 | ALL2020 |
07.08.2020 | 10 | ALL2020 |
01.09.2019 | 99 | ALL2019 |
just create random values with corresponding logic and that's all I need.
I just need the amount column or measure, doesn't matter that much and the periods.
@vojtechsima
Okay Okay I will try my best.
Now one thing is to focus each table has around 30-40 column, but I will only give you the necessary and common columns.
First the "Period (D)" table, under which "period" will be used as a basis for difference. It is categorical text data type values.
period_id | Period |
20204000 | FA 2021 |
20210011 | FA 2020 |
20210021 | Q3 2019 |
20210031 | Q2 2021 |
20210300 | Q4 2019 |
20210600 | Q1 2020 |
20210900 | Q2 2022 |
20214000 | Q3 2022 |
20220011 | FA 2019 |
Next table is "Capital" under which "Amount" will be used as values. Amount here is shown as measures, I still dont know why database administrators has decided to do so.
period_id | Amount | Organisation ID | Junk Dimension ID |
20204000 | 150414983 | 15 | 52 |
20210011 | 1550443577 | 12 | 24 |
20210021 | 3769657488 | 5 | 51 |
20210031 | 2835732574 | 8 | 60 |
20210300 | 1328422901 | 16 | 49 |
20210600 | 1897812930 | 5 | 24 |
20210900 | 2517723972 | 14 | 79 |
20214000 | 4316809220 | 9 | 85 |
20220011 | 2613397245 | 3 | 27 |
Next table is "Division" under which "Division" is being used as row values:
Organisation ID | Division |
15 | Asia |
12 | America |
5 | Latin |
8 | Pacific |
16 | European |
5 | Japanese |
14 | Egypt |
9 | Texas |
3 | Lebron |
Next table is "Analysis" under which "Layers" are being used as Columns:
Junk Dimension ID | Layers |
52 | Explained |
24 | Explained |
51 | Explained |
60 | Explained |
49 | Unexplained |
24 | Unexplained |
79 | Unexplained |
85 | Unexplained |
27 | Unexplained |
There are 7 filters that are being applied on here, which are connected indirectly with all these tables. It will take eternity if I will start explaining how they are related to each other, However if you are still interested in knowing, I can spare my time.
I think you can now provide me a sample PBIX file.
Rest all the explanation on how I need my thing done is given up above.
I just cant thank you enough.
Regards.
To be able to have two slicers you need 2 period tables.
The way to set it up is shown in this thread:
https://community.powerbi.com/t5/Desktop/Waterfall-data-Value-Change-in-Matrix/td-p/2388951
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |