adjust query to exclude holidays and weekends
i wrote this query that calulate work hours (8:30 to 16:30) and need it to exclude holidayes ( i have a seperate holiday table that has holiday start date and holiday end date) and i need it to exclude weekends (friday and saturday) here is the dax power query workhours without out of working hours = VAR WorkTimeStart = TIME ( 08, 30, 00 ) VAR WorkTimeEnd = TIME ( 16, 30, 10 ) VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart ) -- Start and End date/time on current row VAR StartingDateTime = [Created - Copy] VAR EndingDateTime = [Completed - Copy] VAR StartingTime= StartingDateTime - TRUNC ( StartingDateTime ) VAR StartingDate = StartingDateTime - StartingTime VAR EndingTime = EndingDateTime - TRUNC ( EndingDateTime ) VAR EndingDate = EndingDateTime - EndingTime -- Adjust start/end times to fall within working hours. VAR StartingTimeEffective = MIN ( MAX ( StartingTime, WorkTimeStart ), WorkTimeEnd ) VAR EndingTimeEffective = MAX ( MIN ( EndingTime, WorkTimeEnd ), WorkTimeStart ) -- Adjust for hours not worked on StartingDate -- StartingTimeOffset will always be <= 0 VAR StartingTimeOffset = WorkTimeStart - StartingTimeEffective -- Adjust for hours not worked on EndingDate -- EndingTimeOffset will always be <= 0 VAR EndingTimeOffset = EndingTimeEffective - WorkTimeEnd VAR DayCount = EndingDate - StartingDate + 1 VAR TotalTimeInDays = DayCount * WorkingHours + StartingTimeOffset + EndingTimeOffset VAR TotalTimeInHours = TotalTimeInDays * 24 VAR TotalTimeInHoursRounded = ROUNDDOWN ( TotalTimeInHours, 0 ) RETURN TotalTimeInHoursRounded
How do I copy my query from Dax Query View to Power BI dash board?
I have written a Dax Query in Dax Query View. The final result of this query displays a table I would like to display this table on a dashboard. I am new to PowerBI and I am unable to use this. Copying this as a measure gave me syntax errors, though this works in Dax Query View. Can you please let me know how I can use this query and display the resulting table on the dashboard? (Just a note: This is a self-contained portion of a much larger query that could not be done other than through a Dax query, hence had to resort to writing a query) EVALUATE VAR TimeTable = NATURALINNERJOIN( NATURALINNERJOIN( SELECTCOLUMNS( TimeCharges, "id", CONVERT( TimeCharges[Timesheet Id], INTEGER ), "Rate", TimeCharges[Billing Rate], "Amount", TimeCharges[Amount] ), SELECTCOLUMNS( Timesheet, "id", CONVERT( Timesheet[Id], INTEGER ), "ProjectID", CONVERT( Timesheet[Project ID], STRING ), "Qty", Timesheet[Hours] ) ), SELECTCOLUMNS( Projects, "ProjectID", CONVERT( Projects[ID], STRING ), "Project Name", Projects[Name] ) ) Return TimeTable //(The result had 414 rows)
Hep with the formula
Anonymous Anonymous CarlossSainz Hi I have a formula below for calculating the sum of gross risk potential in the table. I want the sum calculation based on the risk name instead of summing up the entire column. I tried the below formula but it is not giving the correct value. I can give an example The total sum of gross risk potential is 100. But the gross risk potential for risk A is 20. I want to get this separate value for all the risks i have in a single column without creating multiple columns. TotalRiskPotentialPerRisk = SUMX( VALUES(Top_Risk_Controls[Riskname]), CALCULATE( SUM(Top_Risk_Controls[Gross_risk_potential]), Top_Risk_Controls[Riskname] = EARLIER(Top_Risk_Controls[Riskname]) ) ) In the above formula, im getting the same values as the gross risk potential column instead of the sum of them based on the risk name. How to get the sum of the values based on the risk name. Please note that the data source is the sharepoint list.
Dynamic Measure Required with two slicer
Hi Folks, I'm trying to develop the dashboard by comparing two months and their uses of two slicers. I prepared the dashboard using Excel Formulas. please help to develop Power-BI As the Raw data is attached Excel is available. please refer it Google Drive Link for Excel File Please feel free to contact for more info if required. RAW Data Period Account Value X Value Y Value Z Fix Tata 4 3 5 Fix Birla 6 7 8 Fix Adani 7 8 9 Fix RIL 8 9 10 Fix Tata 9 10 11 Fix Birla 10 11 12 Jan Adani 11 12 13 Jan RIL 12 13 14 Jan Tata 13 14 15 Jan Birla 14 15 16 Jan Adani 15 16 17 Feb RIL 16 17 18 Feb Tata 17 18 19 Feb Birla 18 19 20 Feb Adani 19 20 21 Feb RIL 20 21 22 March Tata 21 22 23 March Birla 22 23 24 March Adani 23 24 25 March RIL 24 25 26 March Tata 25 26 27 March Birla 26 27 28 March Adani 27 28 29 April RIL 28 29 30 April Tata 29 30 31 April Birla 30 31 32 April Adani 31 32 33 April RIL 32 33 34 April Tata 33 34 35 Regards, MOHIT
