IF Statement on MATRIX returns correct value at top level but lower level is wrong
Hi, I am trying to show the posted revenue for a product before and after the invoice is cleared. We have two value types: Revenue Accrued: this is what we post prior to receive the invoice to have visibility of the incoming revenue Revenue Invoice: this is the amount after the invoice is paid. The rule we apply is that if the "Revenue Invoice" is greater or equal to the "Revenue Accrued" then take the "Revenue Invoice", else take the "Revenue Accrued". I have created two measures for the InvoiceValue and the AccruedValue as showing below: Then Created a new measure "Revenue" where the rule I mentioned above is applied. The result is that the total value is correct (picking up the higher value) but at the Value type level it is showing both values. I want the Revenue Accrued to be blank or zero. Similarly, if I select a previous month where the Accrued value is higher, I want only the Revenue Accrued type to show the value under the Revenue column and the "Revenue Invoice" to be zero or blank. This is a simple example of a more complex report I am currently working on but the issue I am facing is the same. I appreciate any help to resolve this and any explanation of what is wrong. Many thanks in advance, George
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
Summing Multiple IF Statements to get a score
Hi All, I am trying to calculate a score for each row within a table that contain a mark in various columns (example below): Image: Table: Account Red Green Blue Small Medium Large Score [1] [2] [3] [1] [2] [3] 1 X X 2 2 X X 3 3 X X 4 4 X X 4 5 X X 6 So, if a column has an X in it, it needs to add the number underneath the column header for all the columns to give an overall total score. I oringally was do an if statement for each column saying IF column = X THEN column score ELSE 0 and then add them all together, but I couldn't get it to work. Can anyone help? Thanks in advance JonDaily Average Number of Cases Open- Community Services
Dear Members/super contributors, In community services we have to report on daily average number of clients in the care program per month. This is calculated under following conditions: 1. If the client was in care program prior to start of the month and stayed in the care till the end- then we count as number of days in the month. 2. if the client joined the care program within the month then we take datediff between the date of joining and the end of the month date. 3. if the Client left the program within the month under consideration then we do the datediff between the start of the that month and the day they left the program. We add all these numbers and divide them by the number of days in the month to calculate daily average. If someone can help me with syntax of multiple if/ any other function and multiple datediff's or any other way to approach it. My data looks like this and the last column is the intended solution Thanks in advanceHow to convert weekly dates to fortnightly dates
Hi Everyone, Its my first post in the forum so apologies if the post is posted incorrectly. I want to convert weekly dates to fortnightly dates. While this sounds simple - I cant seem to get it working. I know there was a similar post: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Grouping-dates-into-fortnights/m-p/949837 but this didnt quite work for me as the numbers didn't while line up. This is the data I have which goes up to 2023 (see below). I essentially want the 'Fortnight' column to show 06/01/21 for the first period. Then from 7/01/21 - 20/01/21 to show as 20/01/21 and 03/02/21 for the next 2 week period and so on. I intially tried "Fortnight = VAR __BaseDate = DATE(2021,1,6) VAR __2w = (__BaseDate) +14 RETURN if(Dates[Date]<=__BaseDate,__BaseDate,__2w)" but ovbiously this only works for the first period. wondering if some sort of loop could be made using this? or another simpler method altogether? Any help would be greatly appreciated! Kind regards, Mike
