How to overcome error "Function 'DATEADD' expects a contiguous selection..." with bi-dir date table
I have dashboard with different views on email traffic analytics, where I have one visual displaying evolution of reads for first week, and in order to make it work I have a bi-directional relationship between date table and fact table, with second relationship from date table to dim_reads table, which is active only in one specific view. Here is my data model, most of it comes from ODATA transfer, with one dimensional excel stored at onedrive, and two tables which are generated in PQ (one of it is date table): All charts, measures and different pages works fine, but now I am working on a trending view, where I will compare different quarters for different metrics, with possibilities to display per different tags (audience, group, communication topic, etc...). Here is the snippet of planned comparision, where user can select the quarter which they want to compare against previous quarter: And as you can see, all is working well and calculations are correct. The problem starts, when I want to filter it by e.g. audience: As you can see, all calculations are again correct, with the expection of displayed quarters, here is the full error message: Here are the measures which are used for quarters: This one is used in numeric calculations, and it is the quarter which will be selected by user: _SelectedQrt = CALCULATE( QUARTER( MAX('Calendar'[Date]) ) ) And in the card visualisation bellow chart is taking another column from date table, which will display quarter-year in desired format. Here is the measure for taking previous quarter of selected one, also used in calculations: _PreviousQrt = CALCULATE( (fct_SendMessages[_SelectedQrt]), DATEADD('Calendar'[Date], -1, QUARTER) ) And then another measure which is used for card vis: _PreviousQrtKPI = "Q" & [_PreviousQrt] & " '" & RIGHT(YEAR(MAX('Calendar'[Date])), 2) Of course, this can be fixed by changing the bi-dir relationship from date table into single one, but then it will break whole logic on other page, which I dont want. And here are my questions: Why it breaks only that quarter name measure, and not the numeric calculations, which are also using selected/previous quarters in their logic? How to fix this? Ideally, without changing that bi-dir, because then I have to rework quite a chunk of items and currently not sure how to achieve desired output without bi-dir. But given the fact, that all numeric calculations are working well, it shouldnt be necessary to change the relationship, and I hope it will be just some simple fix 😄 Let me know, if you have questions or need some more information related to this, and I thank you in advance if you give this a thought, I am stucked with this already for two days.
DAX Error when using If Statement with DateAdd
https://1drv.ms/u/s!AjIGh95acxebg6JDCZEPNu4K7yIYQg?e=i1Vuqa - PBIX File Hi PB Community i am trying to use If statement in my DAX expression as below new date1 = IF('bi v_shift'[shiftName] = "night" && 'bi v_shift'[startOffsetMinutes] > 55, DATEADD('bi v_shift'[dateTimeNearestHour],-1,DAY),'bi v_shift'[dateTimeNearestHour]). I get the below error "A single value for column 'dateTimeNearestHour' in table 'bi v_shift' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count" All i want is if Shift name is "night" and StartOffsetMinutes is greater than 55 minutes, then the Date in Datetimenearesthour should go back by 1 day. FYI Datetimenearesthour is Date/time type, i changed it to DAte time still getting errors. I am fine with Either Column or Measure lbendlin Ahmedx amitchandak https://1drv.ms/x/s!AjIGh95acxebg6JCE8e6Z9-lxdzq5A?e=buS51O
Last Year measure using DATEADD reporting incorrect total
Hi there, I am trying to create a measure for Last Year metrics, using the DATEADD function. I am using the below, with the measure reporting accurate data by date. Cost LY = CALCULATE(SUM('Table'[Cost (EUR)]), DATEADD('Table'[dated],-1,YEAR)). However, when I present the data by day, including yesterday (relative to the most recent data in the model) the total reported is inflated by quite a bit. As shown in the screenshot, the total for Cost LY over the date range is around 210k, not 369,824. I can't identify where this additonal data is coming from, if someone has seen this before, or knows how the troubleshoot this, it would be a great help.Regarding the DATEADD working with modifier ALL('Date')
Hi all, I have a normal model with 'Date' and 'Sales' tables. And I have 3 measures, The first one is Sales Amount := SUMX('Sales', [Quantity] * [Net Price]) The second one is Sales AllDate := CALCULATE( [Saltes Amount], ALL('Date')) The last one is Sales PM in AllDate := CALCULATE( [Saltes Amount], DATEADD('Date'[Date], -1 ,MONTH), ALL('Date')) I put them in a matrix, but I don't understand the behavior of the last measure, why the ALL modifier doesn't remove the filter of DATEADD? It's like the measure of CALCULATE( [Saltes Amount], DATEADD('Date'[Date], -1 ,MONTH)) Could someone please explain it to me. Thanks!
dateadd in existing measure
Dear everyone I would like to ask for your help. I use a dax measure to visualise a bar chart. I want to shift the bar of March 5 month ahead of August and so forth using the dateadd function. Your help is greatly greatly appreciated. My current measure is this: forecast measure = VAR forecast = CALCULATE ( SUM ( Execution_forecast_tbl[weighted_effort_FOM] ), FILTER ( Execution_forecast_tbl, IF ( OR ( OR ( Execution_forecast_tbl[forecast type] = "MDB forecast", Execution_forecast_tbl[forecast type] = "SF STR" ), Execution_forecast_tbl[forecast type] = "SF TR" ), Execution_forecast_tbl[timescale] > EOMONTH ( TODAY (), 0 ) ) ) ) RETURN forecastPrior month DAX using Measure are not displaying any value
Hi everyone, Need your expertise on this issue. What I want to do is to get the variance between current month and last month. But now I am trying to detect the value of last month first before calculate the variance (just minus-ing the value of current month and last month). Unfortunately, the value is not displaying eventhough I have tried all the other methods that are available online. Maybe I missed something? Need your help for this one :') Here is the fact table : Table A We will need to take the last value of EndDateHourDateTime column as it will keep on adding and accumulate throughout the day. For this problem, we are just going to focus on the value of Defect Class = "Produced Glove". There are also ID column which I didnt include in this screenshot. DimDateTime table : Table DimEndDateHourDateTime Just for reference in case you need to refer the data type available inside this table. Measure 1 : To calculate the sum of the Produced Glove Total Produced Glove = Calculate(Sum(A[Value]), Filter(A, A[DefectClass] == "Produced Glove")) Measure 2 : To calculate the previous month of the Produced Glove (below are some of the failed attempts, a bit messy.. sorry)(tried parallelperiod also, but failed.. sobs) Total Produced Glove last Month = var a = VALUES(DimEndDateHourDateTime[FullDateAlternateKey]) var b = DATEADD(a, -1, MONTH) var c = Calculate(Sum(A[Value]), Filter(A, A[DefectClass] == "Produced Glove")) return Calculate(Sum(A[Value]), Filter(ALL(DimEndDateHourDateTime), DimEndDateHourDateTime[FullDateAlternateKey] in b ), Filter(A, A[DefectClass] == "Produced Glove")) @ Variance = //var a = CALCULATE([@ Card_Date], DATEADD(DimEndDateHourDateTime[FullDateAlternateKey], -1, MONTH)) CALCULATE(SUM(A[Value]), PREVIOUSMONTH(DimEndDateHourDateTime[FullDateAlternateKey])) Not sure, where went wrong.. my table become like this.. not displaying the value Need your help for this one :')
Dateadd formula month to date previous period problem
Hi, I have some probles with measures for for previous period. I created matrix in wich I look at current year from Jan 1st and every day updating data for yesterday (full day). There is first split by week then by date in matrix rows. For curent year it's calculated as it should, but when I calculate for example for period 4 years ago it returns wrong Total at the end of matrix. Subtotals on a week level are fine, but if you see on the bottom there is total up to 31.01.2019 not to 16.01.2019 because I need to have the same period of 4 years ago. But if I choose from date slicer for example 15th instead of 16th (yesterday actualy) it works fine. I need somehow to limit measure to use period 4 years ago until yesterday date. As you see subtotals for weeks in the Total should return 10.553K (01.01.2019-16.01.2019) but it returns 17.979K (01.01.2019-31.01.2019) and that is for whole january of 2019. I used following measures: Sales current year = SUM(DailySales[Sales_CY]) Sales CY - 4 Dateadd = CALCULATE([Sales current year], DATEADD(DATES[Date], -4, YEAR)) I even try with Parallelperiod but it returns whole year, and it is great for some other ideas 🙂 Sales CY - 4 Parallel period = CALCULATE([Sales current year], PARALLELPERIOD(DATES[Date], -4,YEAR)) I have separate DATES lookup table, in which there are split by weeks (Year & Week), and other splits. I ran through many topics and searching through web but I couldn't find solution. Really tried to do it on myself but without any success. Any help will be appreciate greatly. Thanks. Sasha
Same period last year
Hello together I am currently trying to apply YTD to last year. Unfortunately, this works neither with "sameperiodlastyear" nor with a "Dateadd" filter. I would like to display the current value (YTD) and the last year in an identical period in a bar chart. The following formula works with a slicer: = CALCULATE(SUM(TBL_Sales)]),SAMEPERIODLASTYEAR('Date Slicer Choice'[Date])) But I want to solve this without a slicer on the dashboard. does anyone have a tip for me? THX a lot Regards,
DATEADD doesn't work with cumulative sum
Hello. I've got the next problem - i want to calculate WoW, and because there isn't any function i use DATEADD function to calculate values a week ago. But there is a problem i can't solve - it doesn't work with cumulative values (i guess it's the reason of problem). There are 4 measures (for convenience i put them together, also don't mind error lines - i just translated names to English, first 3 work totally fine) - the first one to sum working hours, the second one to gain cumulative sum, the third one to substract cumulative sum from a digit (20), and the forth one should show data a week ago. But it doesn't work properly. For convenience i used 0 interval in DATEADD - the data should be as in the third column, but for some reason it substracts the first column and not the second one as it should be. I'll be very glad if you'll help me to solve this problem (or will give another solution). Have a nice day anyway.
