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.
Use a single value to return multiple metric in bar chart?
Hello all! I have a question, I have a dataste set which looks into sum of Savings Balance for Dec'23 and for Mar'24. I have it summed up and displayed like this in bar Now I need to add a Fixed Deposit metric also that again look into sum for Dec'23 and Mar'24. I figured that having a filter that I can use to switch between Saving Account and Fixed Deposit metric for two dates would be smart, but I cant seem to form a filter that works. I made a new table called Measure Table with the below column, with these options, then a new measure, but it doesnt work. I think im missing something but not sure if theres a way to create a filter that shows value for Savings & FD. SA = IF(CONTAINS('Measure Table', 'Measure Table'[Metrics], "CASA"), [DEC'23], [Mar'24]) If any help that would be great! Thank You!
