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.
Dear Community,
You helped me once, so maybe you can help me again!
I'm calculating the difference of a measure between to date (e.g. "How many Followers did I loose/win compared to last month?").
Now, since no data is available for the current month, it shows the foundation of last month as the difference, which is wrong. I want it to be BLANK. If I do that, however, there is no grand total any more.
How can I solve this?
My measures:
Calculating the difference:
Difference:= CALCULATE ( SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ), fact_netcustomers[dty_id_f] = 2 ) - CALCULATE ( SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ), fact_netcustomers[dty_id_f] = 2, PARALLELPERIOD ( Datum[Datum], -1, MONTH ) )
Version with BLANK():
Difference Blank := VAR var_value = CALCULATE ( SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ), fact_netcustomers[dty_id_f] = 2 ) - CALCULATE ( SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ), fact_netcustomers[dty_id_f] = 2, PARALLELPERIOD ( Datum[Datum], -1, MONTH ) ) RETURN IF ( var_value < -1000000, BLANK (), var_value )
Any help would be much appreciated!
Best regards,
Trojan
Solved! Go to Solution.
hi @v-piga-msft
Thank you for your answer and help!
I was able to solve it differently.
I changed the formula to be more flexible (no hardcoded value), plus, it showed the Grand Total, however, the wrong value. But the rows were right:
Netto-Neukunden Pre-Calc:= VAR var_value = CALCULATE ( SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] ); fact_netcustomers[dty_id_f] = 2 ) - CALCULATE ( SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] ); fact_netcustomers[dty_id_f] = 2; PARALLELPERIOD ( Datum[Datum]; -1; MONTH ) ) RETURN IF ( var_value * -1 = CALCULATE ( [Aktive Kunden]; PARALLELPERIOD ( Datum[Datum]; -1; MONTH ) ); BLANK (); var_value )
Therefore, I calculated the sum over the measure with a different measure:
Netto-Neukunden:= IF ( ISBLANK ( [Netto-Neukunden Pre-Calc] ); BLANK (); CALCULATE ( [Netto-Neukunden Pre-Calc]; FILTER ( Datum; [Netto-Neukunden Pre-Calc] <> BLANK () ) ) )
Best regards,
Simon
Hi @Anonymous,
I have tied to reproduce your issue and made a test demo, you could refer to it.
You could try to modify your formula like below.
Difference Blank := VAR var_value = CALCULATE ( SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ), fact_netcustomers[dty_id_f] = 2 ) - CALCULATE ( SUMX ( fact_netcustomers, fact_netcustomers[fnc_value] ), fact_netcustomers[dty_id_f] = 2, PARALLELPERIOD ( Datum[Datum], -1, MONTH ) ) RETURN IF ( var_value < -1000000 && ISFILTERED ( [ColumnName] ), BLANK (), var_value )
If you need additional help please share some data sample and you can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
hi @v-piga-msft
Thank you for your answer and help!
I was able to solve it differently.
I changed the formula to be more flexible (no hardcoded value), plus, it showed the Grand Total, however, the wrong value. But the rows were right:
Netto-Neukunden Pre-Calc:= VAR var_value = CALCULATE ( SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] ); fact_netcustomers[dty_id_f] = 2 ) - CALCULATE ( SUMX ( fact_netcustomers; fact_netcustomers[fnc_value] ); fact_netcustomers[dty_id_f] = 2; PARALLELPERIOD ( Datum[Datum]; -1; MONTH ) ) RETURN IF ( var_value * -1 = CALCULATE ( [Aktive Kunden]; PARALLELPERIOD ( Datum[Datum]; -1; MONTH ) ); BLANK (); var_value )
Therefore, I calculated the sum over the measure with a different measure:
Netto-Neukunden:= IF ( ISBLANK ( [Netto-Neukunden Pre-Calc] ); BLANK (); CALCULATE ( [Netto-Neukunden Pre-Calc]; FILTER ( Datum; [Netto-Neukunden Pre-Calc] <> BLANK () ) ) )
Best regards,
Simon
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 |
---|---|
116 | |
102 | |
78 | |
77 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |