Previous non-continuous date value
I have a table from where I want to get the value from the previous available date for the specific Country-Indicator. The previous available date can be a day, month or quarter before. I am available to get the inmediate previous date for the specified "Country-Indicator Key", and this is the formula I need support with twiking. Previous Date = CALCULATE( MAX(Indicators[Date]), ALLEXCEPT(Indicators,Indicators[Country-Indicator]), Indicators[Date]<EARLIER(Indicators[Date]) ) What I get from the above DAX is a column that shows the previous available date for the specified "Country-Indicator": Currently I have a work arround that creates a "Previous Key" and then looks for this value on the table, thus bringing the previous value, this can be either calculated column by column or in a single column: Previous Value (not Fancy) = VAR PreviousDate = CALCULATE( MAX(Indicators[Date]), ALLEXCEPT(Indicators,Indicators[Country-Indicator]), Indicators[Date]<EARLIER(Indicators[Date]) ) VAR PreviousKey = FORMAT(PreviousDate,"yyyy-mm-dd") &"-"& Indicators[Country-Indicator] RETURN LOOKUPVALUE( Indicators[Value_Rev], Indicators[key], PreviousKey ) As mentioned I am looking for a way of modifying the "Previous Date" formulato bring the previous value instead of the previous date. I am just looking for a more elegant solution which I am confident it exists. In the link below you can download the files. https://1drv.ms/u/s!Aj1t-UWaJ-akgbRQhZIcveNiSLfYuQ?e=NE5q2Z In advance thanks for your support.
Need help with DAX formula
I am having difficulty in finding out correct way of displaying the below output. I have a table containing columns such as - Contract Name, Contract Start date, Contract end date, Total amount of contract, Status. The output i want to achieve is a bar chart of each month to see which contracts are Live or expired. But each month should carry a total value of contract NOT the allocated value of contract according to number of days in between the start & end date. Example data: Input Contracts Start End Amount Status Con-01 10/01/2020 26/10/2020 110,000.00 Expired Con-02 05/05/2020 09/05/2021 140,000.00 Expired in last 3 months Con-03 04/08/2021 26/08/2022 190,000.00 Not started Con-04 31/12/2020 13/10/2021 30,000.00 Live Con-05 29/02/2020 20/10/2020 80,000.00 Expired Con-06 01/05/2020 31/08/2021 150,000.00 Live Con-07 25/05/2020 24/05/2021 185,000.00 Expired in last 3 months DATA in model: Contracts Month Monthly Amount Con-01 Jan-20 110,000.00 Con-01 Feb-20 110,000.00 Con-01 Mar-20 110,000.00 Con-01 Apr-20 110,000.00 Con-01 May-20 110,000.00 Con-01 Jun-20 110,000.00 Con-01 Jul-20 110,000.00 Con-01 Aug-20 110,000.00 Con-01 Sep-20 110,000.00 Con-01 Oct-20 110,000.00 Con-02 May-20 140,000.00 Con-02 Jun-20 140,000.00 Con-02 Jul-20 140,000.00 Con-02 Aug-20 140,000.00 Con-02 Sep-20 140,000.00 Con-02 Oct-20 140,000.00 Con-02 Nov-20 140,000.00 Con-02 Dec-20 140,000.00 Con-02 Jan-21 140,000.00 Con-02 Feb-21 140,000.00 Con-02 Mar-21 140,000.00 Con-02 Apr-21 140,000.00 Con-02 May-21 140,000.00 So on…. Hence, the output: In July-21, the Con-04 & Con-06 is Live whereas Con-02 & Con-07 is Expired in last 3 months. I want to show Live & expired contracts on each month bar. How can i achieve this? Thank you.Getting the wait time between times in different status and ids in the same column
Hey!! How're you doing? I'm creating a BI to analise some stuff, but i'm having some issues manipulating time values. The data was gettered from mongoDB in a connection with R and, with that all the data came in one, i mean, all de status and its updates came with it's respective time, so i can't calculate between columns for, as all the datatimes lies on the same column, and the power bi won't let me use simple "Datediff" ou calculate just with the column, when it asks for a previews operation like max('status'[datetime]), what i think would just give me the last data. My objective is to adquire the time beetween a change of status for each id on the table, or at least it's maximum and it's mean values. Here's a exemple of what i'm talking about: Id Status Datetime 5ff34563ebaa650023799723 ENTER_QUEUE 04-01-2021 16:42:11 5ff34566ebaa650023799725 IN_ATTEND 04-01-2021 16:42:14 5ff3457bebaa65002379972a FINISH_ATTEND 04-01-2021 16:42:36 5ff3456eebaa650023799727 ENTER_QUEUE 04-01-2021 16:42:23 I've tried to do many thing with that but it's getting weird to do, like i did a table for each status and then merged it's times to compare, and it's the closest I got to what I need. Sorry if it's too basic or not a proper topic to discuss, but i'm kind of new on power bi.
Getting the difference of a value between two seperate time slicers.
Hey Everyone, I'm trying to figure out a measure to give me what the difference between two time slicers are. We're trying to find out how many events has happened in the current date, the previous date and get a measure to determine what the movement between has been. I've seperated the Dates into two sheets form the samesource, one will be my "Comparison Month" and the other "Current Month" The formulas are as follows, and both of them give me the correct answer. Comparison Month= calculate([Count Total Instances],filter('Workforce','Workforce'[Start Date ]= max('Start Date Comparison'[Start Date ]))) Current Month = calculate([Count Total Instances],FILTER('Workforce','Workforce '[Start Date ]=max('Workforce'[Start Date (bins)]))) Problem occurs when I want to make the measure to subtract Current from the Comparison. I use the formua Movement = [Current Month] - {Comparison Month] However, for some reason it appears that the comparison is = 0, as this formula just returns to Current Month E.g. Current Month= 10 Comparison Month = 8 Movement=10 (when it should be 2) Thank you!
Creating a new table with auto-generated Time against expression
Hi, So I have data like this: User Logon Time Logoff Time Person A 03/04/2020 10:00 03/04/2020 12:00 Person B 03/04/2020 9:00 03/04/2020 11:00 Person B 03/04/2020 11:00 03/04/2020 13:00 (etc) My end goal is to have a graph with time on the X axis and count of people logged in on the Y axis. A person is logged in if X is greater than Logon Time but less than Logoff time. It feels incredibly doable and I feel like my first step needs to be to make a new table. I feel like the table's first column should autofill all date/times every 30 minutes from the First logon to the last logoff (or we could use parameters if that's easier?) and then the second column should count each row where Logon < X < Logoff. Is there any way to autofill a new table with these values and then do the counting in DAX? Even a point in the right direction would be much appreciated.Converting seconds to HH:MM:SS text format repeatedly
I have a DAX measure to convert my [Duration in sec] to hh:mm:ss text format. However, if I have several measures which I need to convert to hh:mm:ss text format, e.g. [call Duration in sec], [call Duration in sec wk], [email duration in sec], [email Duration in sec wk], is there a way that I will only create one DAX measure 'Time in Text' that formats them all? Time in Text = VAR Seconds = [Duration in sec] VAR Minutes = INT ( Seconds / 60 ) VAR RemSeconds = MOD ( Seconds, 60 ) VAR Hours = INT ( Minutes / 60 ) VAR RemMinutes = MOD ( Minutes, 60 ) RETURN Hours & ":" & RemMinutes & ":" & RemSeconds
date diff if case open
Hi I am trying to establish an average case processing time for closed cases. I have 3 columns: Start Date/Completed date & Status(open/closed) I am tring to use a CALCUALTE/DATEDIFF/FILTER formula, but i am not getting anywhere. can anyone advise the best dax forumula i can use please? ThanksDAX relative time function to count number of instances between specific times
Hi, I have some data in the following format: Incident Number Date Occurred Incident Name 143 1/1/2001 12:00 ex 1 361 1/2/2001 14:00 ex 2 I am trying to count the number of incidents that have a [Date Occurred] within a certain time frame relative to the current date. I want the number of incidents with a [Date Occurred] between 7 days ago after 12:00 PM to today before 12:00 PM. To give an example: if it is Thursday today, I would like to count the number of incidents with a [Date Occurred] between LAST thursday after 12:00 PM and 12:00 PM this Thursday (today). Ideally, I would like a measure to get this count. Thanks!
