sorting on a joined table custom sort
Hi. I created a custom sort table and joined it to my values table so that I could sort time, on an X axis, alphabetically. For some reason, it starts out sequentially but stops after 8am. This should be relatively simply as I have a table with a custom sort, it's joined on a 1:M, but for some reason, it's not applying it. I have the custom sort field in the 'tooltips' field.
DAX Measure Question
Hi, First time poster, relatively new (but not brand new) to PowerBI. I am create a marketing dashboard for my company, and I am stuck on creating a measure that shows a count of new opportunities by region in the past 30 days. I have got the time filter right, and I didn't think I would need to put anything about the regions in the measure, but the results are telling me otherwise. So here are some details so that wiser minds can help me. The tables are Calendar (date : creation date) CRM Opportunity (organization : Org ID) CRM Region The Measure is: New Opps Last 30 Days = CALCULATE(COUNT('CRM Opportunity'[Opportunity Code]),FILTER(ALL('CRM Opportunity'),'CRM Opportunity'[Creation Date] >TODAY()-30 && 'CRM Opportunity'[Creation Date]<= TODAY())) When I put it in a table, as shown below, it's summing all the new opportunities in the last 30 days correctly, but not discerning any difference between the regions. In the details table, below that, you can see the break between Canada and Central. There are 21 opportunities for Canada, 20 for Central, etc. I assumed the table and the measure would work together, but clearly I'm wrong. Can anyone tell me what I'm doing wrong, and how to do this correctly? I'd really appreciate it! Thanks, Kirstin
Automatically determine which fiscal months are YTD across years
Below is a sample from my Date table. Our fiscal year runs April - March. I have 4 years worth of data. My data is in the "Sales Month" format, I do not receive actual days, just the month in that format. When I loaded my August data a few days ago, my YTD for this year and prior years would be April - August. When I did this in Excel I would add a column to my date table and each month I would add a Y to the new month in all 4 years that designated YTD and I would filter on it. Is there a way to do something like this, but automatically based on the last sales month loaded? TYIA.Consecutive Minutes run and missing days in between
Hi, I'm trying to create the 'CONSECUTIVE MINUTES' column and the 'MISSING DAYS STATUS' CONSECUTIVE MINUTES IS IF THE STATUS REMAINS THE SAME WITHIN THE DAY FOR THE SAME ID, and counting the minutes from the last status if consecutive. 'MISSING DAYS STATUS' should be days missing in between any of the status changes. I'm having a difficulty with this piece and tried using similar approaches. ID DATTIME TIME STATUS CONSECUTIVE MINUTES MISSING DAYS STATUS XYZ 3/13/2023 2:00PM COMPLETED XYZ 3/13/2023 3:30PM COMPLETED 90 BYZ 3/14/2023 9:00AM INCOMPLETE BYZ 3/14/2023 11:00AM INCOMPLETE 120 XYZ 3/15/2023 5:00PM INCOMPLETE 2 BYZ 3/17/2023 9:00AM COMPLETED 3
Convert text value to long time
Hello comunity. I have duration value in sec(integer), I use DAX to convert it to time Movement Time = VAR Duration ='Summary table'[Duration(sec)] VAR Hours =INT ( Duration / 3600) VAR Minutes =INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) VAR Seconds =ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) VAR H =IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ),Hours) VAR M =IF (LEN ( Minutes ) = 1, CONCATENATE ( "0", Minutes ),Minutes) VAR S =IF (LEN ( Seconds ) = 1, CONCATENATE ( "0", Seconds ),Seconds) RETURN CONCATENATE (H,CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )) Then I try appy time format for this column and got error So this perfectly works untill my time value less or equall to 23:59:59. question - how can I convert duration to time like hhh:nn:mm and apply time format as well
Events for a Period time
Hello I'm begings to use Power BI and I'm interesting to know if someone can help. Problem: How to measure the correct Duration by Hour? If I sum the column"Hour" I get a wrong value of 143 instead of 104.18 as table below. My doubt is I need to duplicate the rows breakdown the times or it's possible a metric to analize the data. Start End Duration (sec) hour 2022-09-09T07:58:15.753 2022-09-09T07:58:21.820 6 7 2022-09-09T07:58:21.820 2022-09-09T08:00:38.357 137 7 2022-09-09T08:00:38.357 2022-09-09T08:04:48.450 250 8 Hour SUM_Duration by hour Sum_Duration by period 7 143 104.18 8 250 288.82
Count staff that move between roles
Hi All, I am new to Power BI and I am struggling to create a measure that counts staff of different categories on a particular date. My model is as below: The 'Events' table records include a staff number, post ID, and a date on which that staff member attained that post ID. Each post ID corresponds to a fleet, rank, and status in the 'Post' table. Staff Number Post ID Date 123456 2 09/05/2022 234567 15 14/05/2022 I am trying to count the current number of pilots for each fleet, rank & status, but I cannot work out how to count only the most recent post ID for each pilot. I know that a row-specific context is required to check each staff number, but I am unsure how to confirm a record includes the most recent date in the table for that specific staff number (ie their current post). Here is my DAX code so far: Active Staff = CALCULATE ( COUNTROWS ( Events ), FILTER ( ALLSELECTED ( Events ), Events[Staff Number] = Events[Staff Number] && Events[Date] = MAX ( Events[Date] ) ) ) And here is the output with my actual dataset. There are 238 rows in my 'Events' table whilst there are 202 discrete staff numbers, so I feel like I'm getting close, I just need help to fix the context of the date & staff number comparison. Any advice or guidance would be greatly appreciated! Kind regards, James
