Circular Dependency was detected for Year-To-Date calculation
I am trying to create YTD Last Year, YTD This Year, and then a YoY calculation/column So far I have created the following columns: Max Year = max('Sales WeeklySales'[WeekEndDate].[Year]) Max Date = max('Sales WeeklySales'[WeekEndDate]) YTD TY Sales = VAR maxYear = 'Sales WeeklySales'[Max Year] VAR YTDTYStartDate = DATE(maxYear, 1, 1) VAR YTDTYEndDate = MAX('Sales WeeklySales'[Max Date]) VAR YTDSales = CALCULATE( SUM('Sales WeeklySales'[Validations]), DATESBETWEEN('Sales WeeklySales'[WeekEndDate], YTDTYStartDate, YTDTYEndDate) ) RETURN YTDSales YTD LY Start Date = VAR lastYear = max('Sales WeeklySales'[Max Year]) - 1 VAR YTDLYStartDate = DATE(lastYear,1,1) RETURN YTDLYStartDate YTD LY End Date = VAR lastYear = max('Sales WeeklySales'[Max Year]) - 1 VAR YTDLYStartDate = DATE(lastYear,1,1) VAR maxDateTY = MAX('Sales WeeklySales'[Max Date]) VAR monthDate = MONTH(maxDateTY) VAR dayDate = DAY(maxDateTY) VAR YTDLYEndDate = DATE(lastYear, monthDate, dayDate) RETURN YTDLYEndDate I am then getting an error when I come to create the following: A circular dependency was detected: Sales WeeklySales[YTD LY Sales], Sales WeeklySales[YTD TY Sales], Sales WeeklySales[YTD LY Sales]. YTD LY Sales = CALCULATE( SUM('Sales WeeklySales'[Validations]), DATESBETWEEN( 'Sales WeeklySales'[WeekEndDate], 'Sales WeeklySales'[YTD LY Start Date], 'Sales WeeklySales'[YTD LY End Date]) ) Where is the circular dependency? Thanks in advance!
SUM using DAX for each year
Hello everyone, I have a table with two columns: "Year" and "Sum of Data". Basically, the "Sum of Data" column shows the total amount for each year. Now, I'm trying to get the same total using a DAX formula, which I'll need for future work. But here's the catch: the result I'm getting from the DAX formula doesn't match the automatic sum I'm getting from the "Sum of Data" column. I've included the DAX formula below: AnnualRateTotal = CALCULATE( SUM(DataTable[Rate per 100,000]), ALLEXCEPT(DataTable, DataTable[Year]) ) I've tried to recreate the issue using demo data, but surprisingly, the DAX formula works fine there. Unfortunately, I can't share the main data or the Power BI file. I'm sorry about that. If anyone knows a DAX formula that can give me the same result as the "Sum of Data" column, I'd really appreciate your help. Thank you so much for your help.
function Values doesn t return Table of values but error
Hi, I want to use VALUES function and study from https://dax.guide/values/ that input is table/column and output table (one or more columns) I tried to od it on most simplest case but it failed, can sombebody told me why ? everything seems OK based on DAX guide, what I missed ? Current Data Model My DAX formula Measure = VALUES(Locations[Location]) When I tried to test it on table Measure = VALUES(Locations) I got another error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." I am totally confused, because there is only one condition that input parameter must be table or column, any ideas what I missed ? (Don t think so that DAX.guide is wrong in description) thanks in advance!
Calculated Column not being executed
Hello! I have a table called Progression1 with a calculated column generated with the following code: Rate= CALCULATE(sum(Masterfile1[Awarded]),Masterfile1[STATUS]="GRD",Progression1[Term])/42.34) It works perfectly, and it creates a column with the values that I need. However, when I try to replicate in my second table called Progression2, like this: Rate= CALCULATE(sum(Masterfile2[Awarded]),Masterfile2[STATUS]="GRD",Progression2[Term])/42.34) Nothing happens. I don't get the usual loading screen and seems like the code is not being executed at all. If I click anything afterwards the calculated column goes back to the default Column = Anyone has any idea why does this happens? Is it something on my end? Thank you!Circular dependency when using calculated column
Hello! This is my scenario. I have one table with % rates for different terms. My goal is to display a line graph that shows how the rate increases over time. So, if the rate in the first term was 1% and the rate in the second term was 2%, I want to see it it ploted as 1%, 3%. My approach so far was to create a calculated column(Result) based on a Rate and Value columns, using the value as an index to keep adding the previous Rate to the next one. It worked so far. My problem is that I manually entered the Rate values but want them to be dynamical, calculating them from another table where the data is. What I tried is another calculated column that does that calculation and now I have the ActualRate. When I tried to now substitute the new ActualRate for the original Rate in my Result column. I get a circular dependency error, so I got stuck there. I tried replacing the ActualRate calculated column as a measure, but the thing is that it does not add the previous values, it just displays the term. Here is my code for the Result calculated column: Result = VAR _currentRATE = Progression[Rate] VAR _currentValue = Progression[Value] VAR _sum = SUMX(FILTER('Progression', Progression[Value] <= _currentValue), Progression[Rate]) VAR _last0 = CALCULATE(MAX(Progression[Value]), FILTER(Progression, Progression[Value] < _currentValue && Progression[Value] = 0)) RETURN IF(_currentRATE = 0, 0, SUMX(FILTER(Progression, Progression[Value] >= _last0 && Progression[Value] <= _currentValue), Progression[Rate])) and my code for the ActualRate calculated column or measure: ActualRate = if( CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100 = 0, 0,CALCULATE(sum(Masterfile[Awarded]),Masterfile[STATUS]="GRD") * 100) This is how my columns look like: And this is the current Result (with the measure) agains the expected Result (with the Rate calculated column): Any help is greatly appreciated! Thank you!
Measure XIRR in DAX
Hello all, I have a problem with the Measure XIRR. The calculation is correct if I only access values, date and category in a table. If I then slice the table over another table with a 1:n connection the following error occurs: MdxScript(Model) (106, 34) Calculation error in measure 'Med DAX'[XIRRF]: The XIRR function couldn't find a solution. Apparently it is not possible for me to slice the table with another table. Only if I activate a value in the slicer for the table of the IRR, a result is displayed again. This is also possible for a multiple selection. Does anyone else know this error and can suggest a solution? Or give further support? Many thanks and best regards CarstenNan Error
Jolly Day, Newer to power BI and am getting a NaN error with the below formula. I need to add an if statement I believe but am not sure how to implement it. Could I get some help with this? S Daily Average = CALCULATE(Audit[SUM prozacs]/MAX('Working Days'[Working Days]) + 0) Many thanks, Lydia
