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.
Okay new to PBI...
This is what I'm trying to accomplish.
I have a page and say I have a calculated measure card visual A and B. I also have two date filters on my page, each assigned to either A or B. This is to accomplish showing the difference between two months at the same time.
Then I created a calculated measure (also a card visual) to take the difference (B-A) to show the amount of change from MoM on the same page. The issue here is that, it is not taking the correct difference. The sum is coming back as zero because B is the same formula as A.
I do not have this card visual connected to either of the two date filters.
Using these same sort of techniques, what is the best way to get the difference (b-a) to show simply on this same page?
Solved! Go to Solution.
Hi @Anonymous,
Instead of using two date slicers, you can use just a single date slicer to pick up a date range(i.e. from 2017/05/01 to 2017/06/01). Then the formulas(untested) below should work dynamically in this scenario.
sum of A % difference from June 2017 = VAR minDate = MIN ( 'Table3'[Date] ) VAR maxDate = MAX ( 'Table3'[Date] ) VAR __BASELINE_VALUE = CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( minDate ) }, 'Table3'[Date] ) ) VAR __MEASURE_VALUE = CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( maxDate ) }, 'Table3'[Date] ) ) RETURN IF ( NOT ISBLANK ( __MEASURE_VALUE ), DIVIDE ( __MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE ) )
Regards
With research I just discovered power bi's quick measures and have tried using it. Still with no success though... How can I use quick measures for what I described above?
Okay So I got quick measures to calculate the MoM change... except now it does not change with A &B date filters! Any solution to making a quick measure correspond with filters?
If i understand you correctly, I think you need something like that:
Test = IF ( HASONEVALUE ( 'Filter1'[Filter1] ); SWITCH ( VALUES ( 'Filter1'[Filter1] ); "Filters1_value"; IF ( HASONEVALUE ( 'Filter2'[Filter2] ); SWITCH ( VALUES ( 'Filter2'[Filter2]); "Filters2_value"; [Measure]; "Filters2_value"; [Measure]; BLANK () ); BLANK () ); "Filter1_value"; IF ( HASONEVALUE ( 'Filter2'[Filter2]); SWITCH ( VALUES ( 'Filter2'[Filter2]); "Filters2_value"; [Measure]; "Filters2_value"; [Measure]; BLANK () ); BLANK () ); BLANK () ) )
Hi @Anonymous,
Could you post your table structures with some sample data, and your expected result? So that we can better assist on the formula for the measure.
Regards
sum of A % difference from June 2017 = VAR __BASELINE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date]) ) VAR __MEASURE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date]) ) RETURN IF( NOT ISBLANK(__MEASURE_VALUE), DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE) )
sum of A difference from June 2017 = VAR __BASELINE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date]) ) VAR __MEASURE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date]) ) RETURN IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
So I have two cards called A. They are the same summation measure. They show different numbers because they are each connected to a different date filter that you can see in those photos. Then I want to calculate a # change and percentage change from these two different values. I posted the formulas above. The issue is that the date is hard coded in both those formulas, so if I were to change my date slider to see Jan vs. Feb., for example, the # change and percentage change display as "(blank)".
How can I accomplish a dynamic percentage change and # change using a formula that uses the date filters as a reference or some other solution?
Thank you v-ljerr-msft I will try utilizing this formula.
% Difference calc:
sum of A % difference from June 2017 = VAR __BASELINE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date]) ) VAR __MEASURE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date]) ) RETURN IF( NOT ISBLANK(__MEASURE_VALUE), DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
# difference calc:
sum of A difference from June 2017 = VAR __BASELINE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 6, 1))}, 'Table3'[Date]) ) VAR __MEASURE_VALUE = CALCULATE( 'Table3'[sum of A], TREATAS({(DATE(2017, 5, 1))}, 'Table3'[Date]) ) RETURN IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
So I have two cards called A. They are using the same summation measure. They show different numbers because they are each connected to a different date filter that you can see in those photos. Then I want to calculate a # change and percentage change from these two different values. I posted the formulas above. The issue is that the date is hard coded in both those formulas, so if I were to change my date slider to see Jan vs. Feb., for example, the # change and percentage change display as "(blank)".
How can I accomplish a dynamic percentage change and # change using a formula that uses the date filters as a reference or some other solution?
Hi @Anonymous,
Instead of using two date slicers, you can use just a single date slicer to pick up a date range(i.e. from 2017/05/01 to 2017/06/01). Then the formulas(untested) below should work dynamically in this scenario.
sum of A % difference from June 2017 = VAR minDate = MIN ( 'Table3'[Date] ) VAR maxDate = MAX ( 'Table3'[Date] ) VAR __BASELINE_VALUE = CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( minDate ) }, 'Table3'[Date] ) ) VAR __MEASURE_VALUE = CALCULATE ( 'Table3'[sum of A], TREATAS ( { ( maxDate ) }, 'Table3'[Date] ) ) RETURN IF ( NOT ISBLANK ( __MEASURE_VALUE ), DIVIDE ( __MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE ) )
Regards
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |