Cumulative time difference summarized by person and date
Good morning, I have a dataset of employees the clock in and out every day. I need to summarise the data so that I can calculate how many hours each of them worked each day. A sample of the data looks like this I need a new table that is like this An additional issue here is the the clocking in not showed in chronological order and need to be ordered before calculating the time differences. Many thanks for your help.
Customizable trend analysis for both point-in-time and cumulative data elements (pbix included)
Link to pbix download: https://childdevelop-my.sharepoint.com/:u:/g/personal/dhu_childdevelop_ca/EUl_HgKyoO1NmjwY1F0PypcBDF... In this sample dashboard, I included 3 sample data elements: # new referrals (cumulative) # clients currently waiting for service (point in time) average # days in service for active clients (point in time) I need to create a trand analysis visual for each of these data elements (plan to use line graph for cumulative and bar graph for point in time). The end users would like the following features: Customized date range and responsive point-in-time visuals: when users enter a start and end date (using a date slider slicer?), for example, if they enter 2021-04-01 to today, the "# clients currently waiting for service" visual will show number of clients that were waiting on 2021-04-01, 2021-05-01, 2021-06-01... etc. When a client is waiting on 2021-04-01, their referral date must be before or on 2021-04-01, and their start date must be after 2021-04-01. Customized date range and responsive cumulative visuals: when users enter 2021-04-01 to today, the "# referrals" visual will show number of cumulative referrals since 2021-04-01, this should be a trendline that always goes up If possible, have the option to switch between different time intervals (monthly, quaterly, yearly) for all the visuals. I think this could be achieved using date hierarchy and drill-down, but my end users tend to get confused by that and they want big buttons to click on. So another solution would be create separate visuals, and show/hide them using bookmarked buttons. Is there any other solution? The users want to be able to set the start date as any day of the month/quarter/year, and have the intervals fall on the same day. For example, if the interval is set to quarterly, and the user select "2021-03-02" as start date, the graph will show 2021-03-02, 2021-06-02, 2021-09-02, etc... if this is not possible, setting the dates to first day of the month/quarter/year within the selected time range is fine as well. Thanks so much! I look forward to discussing this with everyone 🙂Reduce total by 1 during a calculated time period.
Hi, I'm a DAX newbie and I'm working to a tight deadline. Despite a herculean effort to learn DAX (many hours) I find I am still woefully short of the skill level required to complete my task, so I am hoping someone can help me meet my deadline while I continue to learn. SITUATION I run a maintenance business and I am trying to forcaste my equipment availablilty into the future based on the number of vehicles in the fleet and when they are due into the maintenance area for servicing or inspection. This graph shows the equipment demand vs availability by date.  With accompanying Table.  The drops in the EquipAv line (Light Blue) are dates that the equipment is due into the maint area for servicing or inspection. This is also shown as a decrease in numbers from "7" in the table. The Raw data comes from the FactMaintSchedule table that holds the MaintDue, ERM (Registration Number), and other information on equipment type and maintenance type. I have removed the Registrations (ERM) for privacy reasons. Vehicle Type ERM Maint Type Maint Event Maint Due LAND ROVER XXXXXX Inspection FMT931 Comms (2)* 08-Nov-21 LAND ROVER XXXXXX Inspection FMT931 Comms (2)* 09-Nov-21 LAND ROVER XXXXXX Inspection FMT931 Comms (2)* 09-Nov-21 LAND ROVER XXXXXX Inspection FMT934 Functional Check (1)* 11-Nov-21 LAND ROVER XXXXXX Inspection FMT932 (W) 6 Monthly Safety Inspection (2) 11-Nov-21 LAND ROVER XXXXXX Inspection FMT934 Functional Check (1)* 12-Nov-21 LAND ROVER XXXXXX Inspection FMT934 Functional Check (1)* 12-Nov-21 LAND ROVER XXXXXX Inspection FMT931 Comms (2)* 15-Nov-21 LAND ROVER XXXXXX Inspection FMT932 (W) 6 Monthly Safety Inspection (2) 15-Nov-21 LAND ROVER XXXXXX Inspection FMT934 Functional Check (1)* 15-Nov-21 HGV XXXXXX Servicing 6 Monthly (1) 16-Nov-21 HGV XXXXXX Inspection Tyre Life Check 2 Wheels 16-Nov-21 HGV XXXXXX Inspection FMT934 Functional Check (1)* 16-Nov-21 HGV XXXXXX Inspection FMT932 (W) 6 Monthly Safety Inspection (2) 16-Nov-21 LAND ROVER XXXXXX Inspection FMT932 (W) 6 Monthly Safety Inspection (2) 17-Nov-21 LAND ROVER XXXXXX Inspection FMT931 Comms (2)* 18-Nov-21 LAND ROVER XXXXXX Inspection FMT934 Functional Check (1)* 18-Nov-21 LAND ROVER XXXXXX Inspection FMT932 (W) 6 Monthly Safety Inspection (2) 18-Nov-21 LAND ROVER XXXXXX Servicing OOPS 5 Yearly Drain/Flush And Replen Cooling System 21-Nov-21 My data model is as follows:  DimEquipmentKey is a bridge table used to bridge a many to many relationship and is not really relevent here. I use it slice the data by equipment type. Both the graph and table above have been sliced by a specific equipment type. Once filtered by date it shows the equipment availability for that equipment, see the measure [EquipAv] below. I am currently using the following simple DAX formulas. [TotalEquipment] =COUNT(DimVehicleFleetMan[ERM] [EquipAv] = [TotalEquipment] - DISTINCTCOUNT(FactMaintSched[ERM] EquipDemand = SUM(FactTAFMISDemand[Required]) Not relevent to this problem PROBLEM When you see the drop in EquipAv line (light blue) this is the date that the equipment is due in for maintenance and is therefore not available for use based on the second measure [EquipAv]. The problem is that I know some of this equipment could be out of use for as much as 10 - 14 days, not just one day. It will probably come in the day before the Maint Due date and could leave again between 1 and 14 days after the Maint Due date, depending on equipment type. OUTCOME I would like to be able to do the following: Create a time period 1 day before and 'N' days after the maint due date depending on equipment. I have already looked at using an EOP / BOP Variable and DATEADD. This got me a time period but I dont really know what to do with it. During this time period I would like to reduce that equipment by 1 every day during the period. Unfortunately I cant share the data due to privacy / security reasons, but I'm happy to answer any questions and will share what I can if asked. Thanks for any help in advance - good luck 🙂 Kind Regards M
Compare report with data from previous period, not previous month,quarter etc
Hi everybody, I have a question that pop up in almost all my report and where i fail to get a good grip on. I work in projects that periodically gather financial report that have to be analysed and used to govern these projects. The question that always comes back is that the project managers always want to see a comparison with the previous report and what the differences in financial performance are. each department has 3 moments a year in which they have to hand in their reports. I have connected a folder where all these reports are saved, so that they will be listed in Power BI. The column VGR indicates the period of the financial report handed in by a department. (VGR1-2021 = the first financial report of 2021 and there are 3 in total), These reports have seperate underlying pages that are connected to other fields: So, i want to be able to make a comparison between the most recent financial reports (indicated by VGR1-2021, VGR2-2021 etc) and the previous financial report. I want to build this in a way that this data automatically changes when new data is uploaded. So if VGR3-2021 is added to the data, i automatically want to generate the comparison between VGR2-2021 and VGR3-2021 instead of VGR2-2021 and VGR1-2021. I already tried many of the DAX formulas like, DATEADD, MTD, PREVIOUSDATE, PREVIOUSPERIOD, but I (or they) fail to generate the right results and i feel it is because most of the posts are more aimed at comparing with the previous year, months or quarter. Since my data is slightly different i fail to get the right formula to establish this. In a previous post i asked a similar questions and got this advice, which worked. However i can't get it to function in my current report. 2nd Latest = VAR latestdate = CALCULATE ( MAX ( 'NPO dump'[index] ), ALL ( 'NPO dump' ) ) VAR secondlatestdate = CALCULATE ( MAX ( 'NPO dump'[index] ), FILTER ( ALL ( 'NPO dump' ), 'NPO dump'[index] < latestdate ) ) VAR currentniv = MAX ( 'NPO dump'[NPO uniek] ) VAR currentpost = MAX('NPO dump'[Post]) VAR result = COALESCE ( CALCULATE ( SUM ( 'NPO dump'[Bedrag] ), KEEPFILTERS ( FILTER ( ALL ( 'NPO dump' ), 'NPO dump'[Post] = "ATB" && 'NPO dump'[index] = secondlatestdate && 'NPO dump'[NPO uniek] = currentniv ) ) ), BLANK() ) RETURN result Does anyone have any advice on how to fix this and how to build my data model in such a fashion that i can use (pretty much) the same formula in other reports? Thanks you!Output rows based on matching date time intervals
Hi, I need help on the same requirement which is handled in the below post, but when there is a many to many relationship. Solved: DAX: Flag order if timestamp is in between 2 dates... - Microsoft Power BI Community Table B will have multiple deal intervals on the same product differentiated using a timestamp. Modified Table B deal_id start_date_time end_date_time product_id 1 02.01.2021 10:00:000 04.01.2021 14:00:00 987654321 2 05.01.2021 8:00:00 05.01.2021 8:25:00 123456789 3 05.01.2021 10:00:00 05.01.2021 10:25:00 123456789 Modified Table A - order positions (multiple orders on same product at different times) order_id timestamp product_id 123 05.01.2021 8:10:00 123456789 456 04.01.2021 16:00:00 987654321 789 05.01.2021 10:15:00 123456789 321 08.01.2021 8:20:15 987654321 654 12.01.2021 6:15:15 345876112 987 15.01.2021 6:10:15 345876112 321 05.01.2021 8:15:00 123456789 Output: deal_id start_date_time end_date_time product_id Order_id 2 05.01.2021 8:00:00 05.01.2021 8:25:00 123456789 123 2 05.01.2021 8:00:00 05.01.2021 8:25:00 123456789 321 3 05.01.2021 10:00:00 05.01.2021 10:25:00 123456789 789 Any lead on this would be of great help! Thanks.
