Calculate variances between various columns depending on the value date vs current date
Hello, I have two tables, one is a Target Amounts table which has a Target Amount and an Outlook Amount. The other one is an Actuals Amounts Table, that has the Actual Amounts in it (Both shown below). These fact values have corresponding 1-many relationships with some dimension tables, so all data can line up as needed. I need to write a measure that returns a value based on the following logic: If the amounts are within 6 months of the current date then: IF: Target Amount > Actuals Amount, return Target Amount - Outlook Amount Else, Return Actuals Amount - Outlook Amount Else, return Actuals Amount - Outlook Amount Intended result: So, if the current date is July 2023, then the measure would return the results in the far right column in the picture below. This is the formula I tried. The results are significantly larger than what was intended (hundreds of Billions in variances when it should be 10s of millions in variances). Any ideas on what I need to tweak? OutlookVarianceCalculation = //Create variables VAR CurrentDate = TODAY() //Current date variable VAR SixMonthsAgo = EDATE(CurrentDate, -6) //6 months prior to current date VAR TargetAmount = SUM(Cumulative_Targets[Current Month Net Target]) //Target amount VAR ActualsAmount = SUM(CMS_Gross_Actuals[Net_Amount]) //Actuals Amount VAR OutlookAmount = SUM(Cumulative_Targets[Outlook]) //Outlook Amount RETURN SUMX( FILTER( //Filter table on dates within the past 6 months ALL('Date Dim'), 'Date Dim'[First_Of_Month] >=SixMonthsAgo && 'Date Dim'[First_Of_Month] <= CurrentDate ), IF( //Return either target vs outlook variance or actuals vs outlook variance based on the logic below TargetAmount > ActualsAmount, //logic TargetAmount - OutlookAmount, // return if true ActualsAmount - OutlookAmount // return if false ) ) + SUMX( //Also sum variances past 6 months using the actuals - outlook logic. FILTER( ALL('Date Dim'), 'Date Dim'[First_Of_Month] < SixMonthsAgo || 'Date Dim'[First_Of_Month] > CurrentDate ), ActualsAmount - OutlookAmount ) Tables: Actuals Table Service Month Posting Date Region Sub Region Reporting Category Actuals Amount 1/1/2023 0:00 1/1/2023 0:00 Region 1 Cat 1 $503,972 2/1/2023 0:00 2/1/2023 0:00 Region 1 Cat 1 $1,099,065 1/1/2023 0:00 3/1/2023 0:00 Region 1 Cat 2 $86,573 2/1/2023 0:00 4/1/2023 0:00 Region 1 Cat 2 $10,277 2/1/2023 0:00 5/1/2023 0:00 Region 1 Cat 2 ($42,746) Target Table Service Date Region Sub Region Reporting Category Target Amount Outlook Amount 1/1/2023 0:00 Region 1 Cat 1 $600,000 550,000.00 2/1/2023 0:00 Region 1 Cat 1 $1,000,000 900,000.00 1/1/2023 0:00 Region 1 Cat 2 $80,000 90,000.00 2/1/2023 0:00 Region 1 Cat 2 $10,000 10,000.00 2/1/2023 0:00 Region 1 Cat 2 ($45,000) (50,000.00) Table Relationships: Each dimension has a dimension table that has a 1-Many relationship with the fact table. The dates used in my dax formula come form my date dimension table.
Taking two conditions and if both are true returning a new value
I have the following table: for which I have created two conditional columns which are as follows: Condition 1 = IF( MDD[Test 10] = "True" && MDD[Out] = "True" && MDD[Less than -10] = "True", "YES!", "") Condition 2 = IF( MDD[Test 7] = "True" && MDD[In] = "True" && MDD[Less than -10] = "True", "YES!", "") Both are text data types. I need to get a result of "PATTERN" to return based on "YES!" being in true for Condition 1 and Condition 2. As you can see, those results are true for Shaggy Rogers, however, I cannot get the return of "PATTERN". I created the column: Con1and2 = IF(MDD[Condition 1] = "YES!" && MDD[Condition 2] = "YES!", "PATTERN", "") However that doesn't work. If I change "&&" to "||" it works, but that is only based on one of the two conditions being met. I need both conditions to be met. I'm also curious why the table adds a new row for each name with every new column. I feel like that might have something to do with the problem as it may not be recognizing that the two conditional results for Shaggy Rogers are the same person. I am lost so any help is greatly appreciated.Adding in a Condition that deals with quotes that feature a recall.
Hi everyone, I'm in need of some help... I'm trying to edit my DAX Measure seen below to add in a condition that would deal with quotes that feature a recall in its history. Currently, the Measure takes the submission date from the final line of the history, and subtracts that by the response date. Essentially, the condition would be able to detect if a quote has a recall in its history and would take the response date from the recalled line and take the place of the submission date in the final line subtraction. I have a screenshot attached below to hopefully provide a good visual example of what I would be looking to do. Below is the DAX Measure that I'd be looking to put the condition into: Quote Turnaround Time for Approval Report Test File = VAR ResponsesThisQuoteVersion = CALCULATETABLE ( DISTINCT ( 'Test Approval Report'[Response Date] ), ALLEXCEPT ( 'Test Approval Report', 'Test Approval Report'[Quote Number], 'Test Approval Report'[Quote Version] ) ) var ThisResponse = AVERAGEX('Test Approval Report','Test Approval Report'[Response Date]) var SubDT = AVERAGEX('Test Approval Report','Test Approval Report'[Submission Date ]) VAR MaxResponse = MAXX( ResponsesThisQuoteVersion, 'Test Approval Report'[Response Date] ) RETURN IF ( ThisResponse <> MaxResponse, BLANK(), MaxResponse - SubDT ) *24 * 60 For what it's worth, I was also able to create calculated columns that would retrieve the previous row's value. Not sure if that's a relevant detail to provide or not. For whomever takes the time to read this and is able to provide suggestions/help, thank you!
Help Needed: DAX Measure to determine if SLA reached the target or not.
Hi Everyone, I'd like to ask for your help. I have data with different targets per SLA. Minimum column is also target but it's the minimum % an SLA must have. I created a measure that I will use to create an icon (using conditional format) to determine if the SLA Summary results met the target or not. _ConditionSLATarget = MAXX('Monthly Summary', IF('Monthly Summary'[_SLA Summary Results] >= 'Monthly Summary'[Target],1, IF('Monthly Summary'[_SLA Summary Results] >= 'Monthly Summary'[Minimum],0, IF('Monthly Summary'[_SLA Summary Results] < 'Monthly Summary'[Minimum],-1)))) ----------------------------------------------------------------------------------------------------- My Table is: 'Monthly Summary' SLA measure is _SLA Summary Results DAX Formula for _SLA Summary Results below: _SLA Summary Results = SUM('Monthly Summary'[Met])/SUM('Monthly Summary'[Volume]) However, there's something strange when I put it in a table. If you notice, DAT 1 SLA should be "-1" with a downward arrow because SLA Summary result of 85.32% did not meet the minimum target of 96% I don't know what went wrong so I'd like to ask for your expertise on this matter. Let me know if you need additional info. Thank you Here's a screenshot of my conditional format for the icons in case you need.How to have an IF statements "if true" Result only apply to what is filtered.
So I have two tables, one contains payment history another contains account information, they are related by accounts. I created this if statement: IF( calculate (sum($), transactions date <= dateadd(accountenterdate,3,months))=0, 0, else calculate (sum($), transactions date <= dateadd(accountenterdate,3,months))) This is the problem, I want the average per account so im using this if statement in an AverageX statement, so that for each row it will calculate how much was paid within 3 months of being booked. For instance I have 8 accounts that were entered on the account table, 7 of the accounts paid and have a transacion history, 1 account did not. So when I do the averagex statement it takes the average of the 7 accounts that paid and are on the transaction table. How can I change it so that even if the account does not have payment history it is still included in the average. right now if i filter to a group of accounts that totals to 8, 7 of which paid, because of the if statement 0 will be provided for all accounts outside the group that made a payment but not the 1 account that has no transaction history. Is there a DOES NOT EXIST option?Writing Measure that Returns Customer Specific Revenue based on Customer Specific Multiplier
Hello, I am attempting to write a measure that examines the customer name before returning a customer specific revenue given a customer specific multiplier multiplied by the amount of items sold. For example, if the customer is customer 1 in this case, then we would take the customer 1 specific multiplier and multiply it by the number of items sold to get the customer 1 specific revenue (if customer = customer 1, then customer 1 multiplier * # of items sold = customer 1 revenue). However, the measure does need to be able to store multiple (up to 30) different customer specific multipliers and in turn know when to use each one based on filtering from a slicer that allows you to choose between customers. I have written the following measure that only appears to work for customer 1 and not the following customers in this example. The numbers 5, 10, and 15 are placeholder customer specific multipliers. Measure1 = SUMX (‘Table’, IF(‘Table’[Customer]= “Customer 1”, ‘Table’[Items Sold]*5, IF((‘Table’[Customer]= “Customer 2”, ‘Table’[Items Sold]*10, IF((‘Table’[Customer]= “Customer 3”, ‘Table’[Items Sold]*15 ….. Here is another measure I’ve attempted but have had no luck with: Measure2 = SWITCH( TRUE(), ‘Table’[Customer] = “Customer 1”, ‘Table’[Items Sold]*5, ‘Table’[Customer] = “Customer 2”, ‘Table’[Items Sold]*10, ‘Table’[Customer] = “Customer 3”, ‘Table’[Items Sold]*15, )) Any feedback and help are much appreciated.If Function not evaluating the expression correctly in Matrix /Table
I am trying to compare the values of a table(B) with a lookup Value( From Table A) I am using FileDate to pick the value AV from Table A using measure,AV_A. AV_A = var dt = SELECTEDVALUE('B'[FileDate]) var av_m = CALCULATE(sum('A'[AV]), FILTER(('A'), 'A'[FileDate] = dt)) return av_m and then using output of AV_A to compare with measure AB_B with the help of measure Test AV_B = (SUM('B'[AV])) Test = IF([AV_A] = [AV_B],TRUE(),FALSE()) Looks pretty simple and easy. But I am perplexed by the result it produced in Matrix or table visualization. I am attached the screenshot by Highlighting the anamoly. I also dont want to show the values at subrow level ('distinct') and Total but want to show True/False for the Top row level ('Field') in matrix. Any insight by the community members will be highly appreciated.
IF Statement Wasting Calculation Time on Measure When Condition NOT Met
Hello, I have come across a curious issue and was looking for some guidance/help. I am working on some financial data that is being given to me in a way where I am having to create a who lookup table using PATHITEM type commands to build a hierarchy. Because of this measures do take a little time to compute (about 8-10 seconds for a couple million records). So, as I move to create projections based on past year averages of values, I am attempting to create an IF or SWITCH structure that reduces the number of calculations down to the lowest possible amount to save visual refresh time by leveraging some toggle switches on the report page for Budget or Trend and a toggle to change the number of past years considered in creating the forecasted average. I found a curious issue though. If I create a very simple: IF( calc_type = 1, 1, 2, 2 ) The compute time is obviously instant. But, if I set the toggle that controls calc_type to 2 and change the code to: IF( calc_type = 1, [do measure], 2, 2 ) I would expect that the return would still be almost instant because the calc_type is not 1, therefor the measure should not calculate but in fact the measure does calculate because now it takes 8-10 seconds to output the number 2. Why is time being wasted by the engine to calculate a measure when the condition to evaluate the measure has not been met? I did find that when I reverse the order and put the 2 condition above the 1, the output is instant again. But if I leave the conditions flipped (2 then 1 instead of 1 then 2) and add both measures: IF( calc_type = 2, [do measure 2], 1, [do measure 1] ) It takes twice as long to evaluate the output which tells me that indeed both measures are being evaluated even though the condition for 1 is not met. Does anyone know why this is happening or have any tips to ensure that 1 and only 1 measure is being calculated instead of calculating those where the condition is not met? Thanks!
