Dax Forumula using power bi
i wanna make some calculation like this : if i filter like this images count calculation like this if i filter this one count calculation like this, if i filter the dept and type total utilization will be interactive, and i wanna make it real time if it possible Flat file relation : real time table i have : if filtered current date : active time, down time and not used keep mooving, cause this is real time data and i wanna ask relation regarding table fuel utillization and reg Data i Wanna Make some total utillization like that so far, i already make it using power bi this is flat file (not real time) the yellow mark, already correct but the red one still wrong, i wanna convert it to percentage SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 --AND vm.internal_id = 'DTOF000004' --AND vm.start_date_vhl_down >= '2024-02-01' --AND (vm.finish_date_vhl_down IS NULL OR vm.finish_date_vhl_down <= '2024-02-29') AND vm.status = 'C'; SELECT vo.internal_id, vo.operational_date AS start_date, vo.operational_date AS finish_date, 'OPERATIONAL' AS trx, '' AS shift, vo.time_start AS start_time, vo.time_finish AS finish_time, '' AS duration, vo.note, vo.status AS vehicle_status, vo.dept_id, vr.vehicle_id, vr.id_type, vr.type, vr.sub_type, vr.owned, vr.rent, vmd.value, vmd.detail, vt.id_type AS id_type_2 FROM vms_vhl_operational AS vo JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vo.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vo.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 --AND vo.internal_id = 'DTOF000004' --AND vo.operational_date BETWEEN '2024-02-01' AND '2024-02-29' AND vo.status = 'C'; --/STEP 2/ (Crossing date ) SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) --AND vm.internal_id = 'DTOF000004' --AND ( vm.start_date_vhl_down BETWEEN '2024-02-01' AND '2024-02-29' ) AND vm.status = 'C'; SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) --AND vm.internal_id = 'DTOF000004' --AND ( vm.finish_date_vhl_down BETWEEN '2024-02-01' AND '2024-02-29' ) AND vm.status = 'C'; SELECT vm.internal_id, vm.seq, vm.start_date_vhl_down AS start_date, vm.finish_date_vhl_down AS finish_date, vm.type AS trx, '' AS shift, vm.start_time_vhl_down AS start_time, vm.finish_time_vhl_down AS finish_time, '' AS duration, CONCAT(COALESCE(vm.start_vhl_down_note, ''), ';', COALESCE(vm.finish_vhl_down_note, '')) AS note, vm.status AS vehicle_status, vm.dept_id, vmd.value, vmd.detail, vr.vehicle_id, vr.type, vr.sub_type, vr.owned, vr.rent, vr.id_type, vt.id_type AS id_type_2 FROM vms_vhl_maintenance AS vm JOIN vms_vhl_mst_dept AS vmd ON vmd.id = vm.dept_id JOIN vms_vhl_reg AS vr ON vr.internal_id = vm.internal_id JOIN vms_vhl_mst_type AS vt ON vt.id = vr.id_type WHERE 1 = 1 AND MONTH(vm.start_date_vhl_down) <> MONTH(vm.finish_date_vhl_down) --AND vm.internal_id = 'DTOF000004' --AND ( vm.start_date_vhl_down < '2024-02-01' AND vm.finish_date_vhl_down > '2024-02-20' ) AND vm.status = 'C'; can some one help me ?? rajendraongole1 lbendlin Ritaf1983 Kedar_Pande SamWiseOwl parry2k
Need help! Apply minimum wage increase for applied month and all months after
Hi All! I am trying to calculate base rates for employees by month. I have minimum wage changes for different employees by month. I have the wage changes as a % by the employee as an import. I also have their base rates. These two tables and the Year & Month table are all connected by valid and active relationships. I am trying to create a new measure that takes the base rate and multiplies by the wage change (with keeping the regular base rate if there is no wage change). BUT, I want that new wage to stay for future months. Ex: Employee A has a rate of $15 for beginning of 2024 for each month. Starting in July, they have a % change of 103%. I would like July, August, September, etc. to show the new wage of 15+103%. Thank you SO much!!!!!!!!!!period wise division of categories
im trying to show the percentage of each ttp period wise. that is.... i want to divide ttp_ty_1 of period 6 with its total 18763168.91. and as soon as i click period 6 in slicer..... the table should display ttp_ty_1 to ttp_ty_12 of 6th period....i want same thing for other periods also.... for example: period 6 has ttp_ty_1 value as 4864947.29.....and total value of ttp_ty_1 of period 6 is 18763168.91 so i need a formula which divides ttp_ty_1 by total....then similarly ttp_ty_2 value by its total 20202236.1 etc......thus each period will have 12 valuesNeed Help with DAX 2-Day Lag Formula return different data sources based on future vs. past
Hi, I am trying to create a DAX formula that will return hours depending on the day. Please help! I am very stuck. For past dates before TODAY and YESTERDAY, I want to return the data from "Actual Hours" measure/column. For TODAY, YESTERDAY and FUTURE DAYS, I want to return the data from "Scheduled Hours" measure/column. I am currently using the below formula but it is not bringing in "Scheduled Hours" for today and future. The hours return for past dates before Yesterday and Today are incorrect. I am comparing this data in a table matrix by bringing in Actual Hours and Scheduled Hours side by side. 2 Day Lag Hours = VAR CurrentDate = SELECTEDVALUE('Tables- Dates'[Date]) VAR Result = SWITCH( TRUE(), CurrentDate < TODAY() - 1,[Actual Hours], //Past: Actual Hours Measure CurrentDate <= TODAY(), [STAR Scheduled Hours], //Today and Yesterday: Scheduled Hours Measure CurrentDate > TODAY(), [STAR Scheduled Hours] //Future: Scheduled Hours Measure ) RETURN Result Thank you!DAX Measure based on multiple fields
Hi, I am trying structure my calculations using DAX and i cannot change my datamodel. I need to calculate two measures Rating Measure - this is calculated by considering sum of above/ sum of under from format column when setting is Rating Target Measure- this is calculated by considering sum of above/ sum of under from format column when setting is Target I achieved this calculation in powerquery but i wanted to use DAX for other reasons. Can you help in providing tips to achieve these measures? Date Product Format Setting Value Jan-23 Lux Above Rating 20 Jan-23 Lux Under Rating 30 Jan-23 Lux Above Target 30 Jan-23 Lux Under Target 40 Feb-23 Lux Above Rating 30 Feb-23 Lux Under Rating 40 Feb-23 Lux Above Target 40 Feb-23 Lux Under Target 50 Mar-23 Lux Above Rating 45 Mar-23 Lux Under Rating 45 Mar-23 Lux Above Target 40 Mar-23 Lux Under Target 50 Expected solution for rating and target measure: Thanks for your help
