New to Power BI and stuck trying to create date measures
Hello, I'm a complete Power BI newbie and trying to get my head round some of the features and some simple functions. I've managed to set up initial data source table and run create some initial visualisations on the data in the table, but now I'd like to take next step and do some more analysis relating to dates. In my initial table called "Live Campaign List" I have two date fields: - Date Started Planning - Date Sent to Current Position I'm trying to create measures using the DATEDIFF function to calculate: Power bi date difference (in days or months) between the two columns Power bi date difference from today (again in days or months) This is probably too advanced for me, given I can't master the basic functions, but ultimately I think it would be good to determine a categorisation of risk - Red/Amber/Green based on length of time difference (ie those with longer time gap and priority level of campaign). I have tried to use a DAX calcuation as follows: Date Difference Interval Measure = DATEDIFF('Live Campaigns List'[Date Started Planning], 'Live Campaigns List'[Date Sent to Current Position], Day) but I get an error message saying that in the "Date Started Planning" a single value cannot be determined. What am I doing wrong? How do I get the calculations to work? Thanks in advance EdDAX Delta Issue - Difference Between Dates Not Working When Amount is Zero for Maximum Date Selected
I am trying to accurately calculate the difference in the amount sold between the most recent date selected and the oldest one. I used a DAX formula to create my Delta, and when calculating the difference between dates, my Delta works for most scenarios. However, there are cases where the measure is not working, specifically when we have 0 for the maximum date and a positive value > 0 for the minimum date. Here is the DAX I am using for my Delta, in my Measure: Diff = IF ( HASONEVALUE ( Table[Snapshot_Date] ), ( ( SUM(Table[Orders]) ) ), CALCULATE ( ( SUM(Table[Orders])), FILTER ( Table, Table[Snapshot_Date] = MIN ( ( Table[Snapshot_Date] ) ) ) ) - CALCULATE ( ( SUM(Table[Orders])), FILTER ( Table, Table[Snapshot_Date] = MAX ( ( Table[Snapshot_Date] ) ) ) ) ) The screenshot below shows that my Delta works perfectly when calculating the difference for the selected dates and rows below. Here follows one example where my measure is not working, where I have 0 for the maximum date and a positive value > 0 for the minimum date: For the highlighted row, the delta should be -0.1 instead. All other rows in this matrix are making sense. Can someone please help me out with this issue? Thank you 🐾
Calculate the difference between values of a column based on a number of rows
Hello, I'd really appreciate you can help me. I have a table like this one: Basically, it's showing the number of cases by day and country. The list has many rows. I need to add a column that shows the difference between the number of cases of that day and the number of cases 14 days before. I'm not familiar with DAX, and I know neither how to refer to the specific value of a cell in a column nor how to specify the number of rows behind to look for the difference's value. I really appreciate any help you can provide. Best PedroCalculating value differences for each week
I have some 'availability' numbers (a percentage) for a bunch of machines on a weekly basis. My raw CSV data looks like this: Machine,WW,Availability A,WW35,0.9 B,WW35,0.95 C,WW35,1 D,WW35,0.87 A,WW36,1 B,WW36,1 C,WW36,0.84 D,WW36,0.94 A,WW37,0.75 B,WW37,0.98 C,WW37,0.91 D,WW37,0.89 A,WW38,1 B,WW38,0.88 C,WW38,0.99 D,WW38,0.95 Data source is updated weekly and new Work Week (WW) availability data is added for each machine. A machine is deemed 'Pass' if the availability for that week is > 90%. I calculate the 'Pass' measure as below. Pass = VAR varCount = CALCULATE(COUNTA(data[Availability]), data[Availability] > 0.9) RETURN IF(varCount = BLANK(), 0, varCount) Pass count for each machine for each week, displayed in a matrix, looks like this (given above data): Now, I want to calculate some figures for these pass values for each machine. My actual needs are a bit complex, but few of the most basic things I wanted calculated are shown below. New Pass Number of total machines for each week that passed, but failed previous week. New Fail Number of total machines for each week that failed, but passed previous week. Steady Number of total machines for each week that the condition didn't change. To better illustrate, I put my desired results in an Excel file: As mentioned at the beginning of the post, my source CSV is updated with new data each week, so as time goes on I will have more [WW] columns added in my PowerBI matrix. Given this I don't quite know how I can calculate the above values dynamically without hardcoding anything. Is this possible?Get difference in Matrix between two dates of a slicer (show zeros)
Hi everyone, I'm having problems creating a measure that can calculate the difference between 2 dates in a slicer, i created this one but doesn't seems to work like i would want: DELTA2 = VAR LASTDAY = [HOY] VAR PREVDAY = [ayer] VAR TOODAY = CALCULATE(SUM('DATA BASE'[Tons]),'DATA BASE'[FECHA DE CAPTURA] = LASTDAY,VALUES('DATA BASE'[OP])) VAR YESTERDAY = CALCULATE(SUM('DATA BASE'[Tons]),'DATA BASE'[FECHA DE CAPTURA] = PREVDAY,VALUES('DATA BASE'[OP])) RETURN TOODAY-YESTERDAY where: [HOY] = CALCULATE(MAX('DATA BASE'[FECHA DE CAPTURA]),ALL('DATA BASE')) and, [ayer] = CALCULATE(MAX('DATA BASE'[FECHA DE CAPTURA]) - 1,ALL('DATA BASE')) my result is this: it only kinda works with the last 2 dates, also i filtered the results of DELTA2 = 0, so it only displays the changes between days. I would like to reach a matrix like this (whatever date i choose, will be always one day and the next): if anyone can help me, i would really apreciatted.
