Unable to get accurate result when comparing current data with previous data based on the filter con

Question

Hello Team,when I compare the live table data with historical table data it is working&nbsp; fine. If I use filter condition then figures are slightly changing and trends are not working . Request you&nbsp; please provide solution for this&nbsp;&nbsp;I used dax formula like this&nbsp;&nbsp;Previous week = snapshot table and this week = Opportunity table&nbsp;&nbsp;Snapshots_Unweigted Orderentry_Budget% =&nbsp;IF(RIGHT(SELECTEDVALUE(Snapshots[Fiscal Period]),4)&amp;"-"&amp;LEFT(SELECTEDVALUE(Snapshots[Fiscal Period]),2) &lt; [Current_Quater], DIVIDE([ActualsWon_IND],[Budget_IN],0), IF(([ActualsWon_IND] &lt; [Budget_IN]), [TotalOE]/([Budget_IN]-[ActualsWon_IND]), DIVIDE(([TotalOE]+[ActualsWon_IND]),[Budget_IN],0)))&nbsp;&nbsp;Opportunities_Unweigted Orderentry_Budget% =&nbsp;IF(RIGHT(SELECTEDVALUE(Opportunities[Fiscal Period]),4)&amp;"-"&amp;LEFT(SELECTEDVALUE(Opportunities[Fiscal Period]),2) &lt; ([Current_Quater]), ([c_ActualsWon_IND]/[c_Budget_IND]), IF([c_ActualsWon_IND] &lt; [c_Budget_IND], DIVIDE([c_TotalUOE],([c_Budget_IND]-[c_ActualsWon_IND]),0),DIVIDE(([c_TotalUOE]+[c_ActualsWon_IND]),[c_Budget_IND],0)))&nbsp;&nbsp;Trends = [Opportunities_Unweigted Orderentry_Budget%]-[Snapshots_Unweigted Orderentry_Budget%]&nbsp;These formulas are working fine without filter conditions . If i use Slicer Region wise, Ex: North America, Northern Europe, Southeren Europe and select any one of that then all 3 formulas are not giving the accurate result.&nbsp;Snapshots: not accurate&nbsp;&nbsp;Opportunities :&nbsp; Not accurate&nbsp;&nbsp;&nbsp;Trends : Coming wrong data.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Kind Regards,Siva&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

allisonkennedy · Answer

siva_yellanuri&nbsp; Your DAX looks like a rather complex text manipulation - what is your data source? Can you do some of the RIGHT() and LEFT() functions in Power Query instead of DAX? What does your data model look like? Having your data model set up as a proper star schema will help. Can you share your relationships and raw data and some more info?&nbsp;
Here's some blogs I wrote on star schema that may help too:&nbsp;Excel with Allison: Search results for complicated

siva_yellanuri · Answer

Hi Allison,Thanks for your reply. Actually data is coming from the Business application so that is live data i wont get Report&nbsp; view option to go to power query to update the changes instead of DAX. There is no relationships between snapshots, Opportunities tables. By using the Treatas function i make the relationship between these tables. I cann't send the Raw data. Request you please assist me why the figures in the tables are not accurate if i filter the data in the slicer.

anonymous · Answer

Hi&nbsp;AllisonKennedy&nbsp;,
Thanks for the quick response.
Hi&nbsp;siva_yellanuri&nbsp;,
I have some information to add.
Ensure that your slicers are correctly configured to interact with both your&nbsp;and&nbsp;tables. If your slicers are not properly connected to these tables, it could lead to inaccurate filtering and, consequently, incorrect results.&nbsp; 
More details :&nbsp;https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers.
The behavior you're experiencing could be due to how the filter context is applied when you select a region from the slicer. It's important to understand that slicers change the filter context, and this can affect your DAX calculations. More details :&nbsp;https://learn.microsoft.com/en-us/power-bi/guidance/dax-filter-context.
&nbsp;
&nbsp;
How to Get Your Question Answered Quickly - Microsoft Fabric Community
&nbsp;
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
&nbsp;
Best RegardsCommunity Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

allisonkennedy · Answer

&nbsp;How are you getting on with solving this problem?&nbsp;
&nbsp;
We need a bit more detail from you still around how your tables are related, TREATAS function is powerful, but as&nbsp;&nbsp; mentioned, you need to be clear about which filters you're using and ensure they're related to the tables in your report or the measure that uses the TREATAS.&nbsp;
Can you share your treatas measures?
&nbsp;
Also, careful with your &lt; and &gt; as you're using these in a text field, so they may not behave how you expect.
&nbsp;
I have used Daxformatter.com to format one of your measures, makes it a bit easier to read:
&nbsp;
Opportunities_Unweigted&nbsp;Orderentry_Budget%&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;RIGHT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;Opportunities[Fiscal&nbsp;Period]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&amp;&nbsp;"-"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&nbsp;LEFT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;Opportunities[Fiscal&nbsp;Period]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&lt;&nbsp;(&nbsp;[Current_Quater]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;[c_ActualsWon_IND]&nbsp;/&nbsp;[c_Budget_IND]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[c_ActualsWon_IND]&nbsp;&lt;&nbsp;[c_Budget_IND],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[c_TotalUOE],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;[c_Budget_IND]&nbsp;-&nbsp;[c_ActualsWon_IND]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DIVIDE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(&nbsp;[c_TotalUOE]&nbsp;+&nbsp;[c_ActualsWon_IND]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[c_Budget_IND],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))
&nbsp;
&nbsp;
Anonymous
siva_yellanuri

Forum Discussion

Unable to get accurate result when comparing current data with previous data based on the filter con

4 Replies

Related content

Count of values not accurate

Comparing values to previous months

Compare Against Previous Period KPI Card

Compare previous and new sales

Comparing perfomance previous years