Return prior value if no value exists for current Month/Year
Hi, I'm having trouble with some dax. This is working as expected for a month with values and returning the prior date. If the current Month/Year does not have a value, it will return the prior month/year. What I am having trouble with is returning the value for that period. For example, May 2023 has no value but we can see that April 2023 has a value. I would like to display the April 2023 value or the previous value until there is a value with a date that is >= selected date. _TEST1 Dated Avg Labor Rate = var _minsel = MAX(DimDate[Date]) var _selected = LASTNONBLANK(FILTER(ALL(DimDate[Date]), DimDate[Date]<=_minsel),[Hmmm]) Return _selected Not seeing April value forward. April Value For reference: Hmmm = sum(VW_SPECIAL_CUSTOMER_LABOR_RATE_UNIONED_VIEWS[LABOR_RATE])
Calculate Number of hours between first and last time stamp
I am calculating Number of scan/hour for the warehouse forklift drivers. It shows output for current day, previous day, week to day, MTD and FY YTD. For calculatin number of Hours, till now I was using 7.5 as shift hours, but, I need to calculate hours based on first scan done by driver and the last scan by driver(timestamp). How can I calculate this hour difference. The time stamp column looks like this (It doesn't have any start or end, it is just one coloumn)- For example, for current day I was using:- // I took 6 as start time of shift- Current Day = VAR CurrentDate = MAX('Calendar'[Date]) RETURN DIVIDE( CALCULATE(SUM('ULDs - History'[lines]), 'Calendar'[Date] = CurrentDate), If((((HOUR(NOW())-6)*60+MINUTE(NOW()))/60)>7.5,7.5,((HOUR(NOW())-6)/60) ) Previus day- Previous Day = VAR CurrentDate= MAX('Calendar'[Date]) VAR Prev_Day = CALCULATE(MAX(Calendar[Date]), FILTER('Calendar', Calendar[Date] < CurrentDate && 'Calendar'[If work day]=1)) VAR Trasnactions_Prev_Day = CALCULATE( SUM('ULDs - History'[lines]), 'Calendar'[Date] = Prev_Day ) RETURN DIVIDE( Trasnactions_Prev_Day, 7.5, 0 ) Week to date- VAR CurrentDate = LASTDATE('Calendar'[Date]) VAR DayNumberofWeek = WEEKDAY(LASTDATE('Calendar'[Date]),2) VAR Transactions = CALCULATE( SUM('ULDs - History'[Inner]), DATESBETWEEN('Calendar'[Date], DATEADD(CurrentDate, -1*DayNumberofWeek, DAY), CurrentDate)) VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[If work day] = 1, DATESBETWEEN('Calendar'[Date], DATEADD(CurrentDate, -1*DayNumberofWeek, DAY), CurrentDate)) VAR TotalHours = (DayNumberofWeek-1) * 7.5 + (((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60) RETURN DIVIDE( Transactions, TotalHours, 0 ) MTD = VAR Transactions = CALCULATE( SUM('ULDs - History'[Inner]), DATESMTD('Calendar'[Date]) ) VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[Month(Current_or_Previous)] = "Current Month", 'Calendar'[If work day] = 0) VAR CurrentDate = MAX('Calendar'[Date]) VAR TotalHours = (DATEDIFF(DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1), CurrentDate, DAY) - holidays) * 7.5 +/*(Diff of dates - holidays) * 7.5*/ (((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60) RETURN DIVIDE( Transactions, TotalHours, 0 ) FY YTD = VAR Transactions = CALCULATE( SUM('ULDs - History'[Inner]), DATESBETWEEN ( 'Calendar'[Date], DATE(YEAR(MAX('Calendar'[Date])), 7, 1), TODAY () ) ) VAR CurrentYear = MAX('Calendar'[Fin Year]) VAR holidays = CALCULATE(COUNTROWS('Calendar'), 'Calendar'[Fin Year] = CurrentYear, 'Calendar'[If work day] = 0) VAR CurrentDate = MAX('Calendar'[Date]) VAR TotalHours = (DATEDIFF(DATE(YEAR(CurrentDate), 7, 1), CurrentDate, DAY) - holidays) * 7.5 + /*(Diff of dates - holidays) * 7.5*/ (((HOUR(NOW()) - 6)*60 + MINUTE(NOW()))/60) /*hours of today untill now */ RETURN DIVIDE( Transactions, TotalHours, 0 ) How can I edit all these formulas to calculate total hours based on first time stamp and last time stamp of that day? Fowmy parry2k amitchandak v-diye-msft Jihwan_Kim Ashish_Mathur
DAX to color the font into red or cell into red for weekends in Table Matrix visualization
Hi All, I want to color the font or the cell containing the weekends[ Saturday and Sunday] into red in table matrix. Is that possible ? Can we handle it using DAX? Please suggest My sample input data is as below :- Date TotalSeats OccupiedSeats School Name Buildings 21-03-2021 10 1 School1 Building1 22-03-2021 10 4 School1 Building1 23-03-2021 10 4 School1 Building1 24-03-2021 10 1 School1 Building1 25-03-2021 10 2 School1 Building1 26-03-2021 10 1 School1 Building1 27-03-2021 10 1 School1 Building1 21-03-2021 12 5 School1 Building2 22-03-2021 12 8 School1 Building2 23-03-2021 12 8 School1 Building2 24-03-2021 12 3 School1 Building2 25-03-2021 12 4 School1 Building2 26-03-2021 12 2 School1 Building2 27-03-2021 12 4 School1 Building2 Expected Output: it should either color the cell containing weekend[Saturday and Sunday dynamically] as red or it should color its font as red.
