bus day sla dax
hi i have the following tables-but cant obtain bus day sla it seems- i have obtained calendar day sla to measure days(that part is working and giving me my data) but i need to go further and now convert to business days sla between same two columns i also created a holiday table and a weekday value table-what is best formula here to convert calendar day sla to bus day sla? below is the calendar day sla that is working and the two tables being compared on days- additionally i created these two tables-weekday value 1-7 holiday table
I need to show Previous Complete value against the current year
Hi everyone, I need help with this DAX to achieve a result. I am trying to get the previous year's value for the current year. The issue is with the last year. In the last current year, data is available only until October, so I am getting previous values only until October. However, I want to show the entire previous year's value. i want to 186 instaed of 145 Measures i used: measure 1: Running_Total_Employees VAR Maxd_ = max('Calendar'[Date]) Return CALCULATE([Total_Employee Count ], 'Calendar'[Date] <= Maxd_, all('Calendar'[Date])) Measure 2: 2_test_running_total CALCULATE([Running_Total_Employees], SAMEPERIODLASTYEAR('Calendar'[Date]))
Showing different month names for months using format(). How to get the correct month names??
Hello, I am trying to extract the month name from the date column using the format([date], "MMMM"). But I am getting different month names. Help me to get the correct month name. Pls refer to the image attached:
Previous Month Sales with Month Selected in Slicer
Hi All, I got stuck with a DAX measure. I need to calculate the Current Month's sales and the Previous Month's sales. Both the measures are working perfectly if nothing is selected in the date or month slicer. when I select a month in the slicer then the Previous Month's sales measure is giving a blank. For example, if I select "June" in the slicer table then the Current Month's sales measure should be of June and the Previous Month's sales measure should be of May. My measure are given below: Current Month Sales = TOTALMTD ( SUM ( 'Dataset supply chain'[total sale] ), DATESMTD ( 'Dataset supply chain'[Order date] )) Previous Month Sales = CALCULATE ( SUM ( 'Dataset supply chain'[total sale] ), PREVIOUSMONTH ( DATESMTD ( 'Dataset supply chain'[Order date] ) )) I am also attaching screenshots of the results. Looking forward to a solution. Thanks, SidDATESMTD misbehaving if Sales in current month are zero
If Sales for Salesperson in current month = 0, the following DAX will then fetch the Sales Amount for the last month that it can see Sales (at least I think this is what is happening) Sales MTD = CALCULATE([Amount Measure], DATESMTD('NAV_Sales History MASTER'[Posting Date])) So in this example, Sales for Jan are £0 but it is returning Sales for December For such a simple piece of DAX I'm struggling firstly to understand why it would retrieve the incorrect figure, and then why it would start looking for something outwith the current month? Totals for Salespeople who have sales in the current month are displaying fine. Any ideas at all?
DAX formula to calculate rolling X average based on parameter & values from other columns
Hi all, I am trying to create a Column using DAX that calculates a rolling 8-week average based on a parameter/measure with multiple filters depending on the values of other columns. Data Structure: There is also a parameter for the current week number in cell K2: The column Forecast is what I am trying to create using DAX. What it does is: - If the week number is less than or equal to the Current Week, just copy the value under "Actual" (Column E) - Else, get the average of "Actual" (Column E) of the 8 weeks prior to the current week (if Current Week = 38, get average of weeks 30-37), subject to having the same FY, same KPI, and same Owner with the current row. In Excel, the correct formula for Row #2 would be: =IF( B2<=$K$2, E2, AVERAGEIFS( E:E, B:B,"<"&$K$2, B:B,">="&$K$2-8, D:D,D2, C:C,C2, A:A,A2 ) ) I am really struggling to find the right DAX syntax because I can't seem to figure out how to pass one of the values of the current row into the DAX filters. I tried doing something like this (without the IF statement) but obviously it's not working: I have uploaded the sample Excel and PBIX files for easier reference. You can see how the correct calculation is done in Excel. - XLSX: https://docs.google.com/spreadsheets/d/1fYG1dRtkthvLhxMTDXxbx1x_VmoVxf0Y/edit?usp=sharing&ouid=111734964497020988818&rtpof=true&sd=true - PBIX: https://drive.google.com/file/d/1TqT_xKZg3s7sSyZJr4yrQPtt-iW4mU8I/view?usp=sharing Any help will be greatly appreciated! Cheers, Andrew
DAX formula to show the Attendance date greater than equal to System date using -UTCNOW() or NOW()?
Hi All, I have a Student table with 5 columns. I have a typical issue where I need to show the datetime for student AttendanceDate for region Armonk and region London . I want any user from USA should only see the records of today's attendance date but for USA [Armonk]it is showing yesterday's date entries [marked in red in the below screenshot]and but for any user in Europe are able see the attendance of today's date . Could anyone suggest any DAX to handle this? I tried the below DAX but it didnt show any record for USA while it showed correct record for Europe region.I am located in Europe region and I published the report from my local machine? Does that make a difference? DAX calculated column formula :- SYSTEM_TIME_REF = NOW() FLAG = IF(student[attendance_date] >= student[SYSTEM_TIME_REF],0,1) Input data source : AttendanceDate Location StudentID Name Region 20.08.2021 11:05AM Gate1 101 Tom London 20.08.2021 10.03.22 Gate1 102 Hary London 20.08.2021 10.09.21 Gate2 103 Rohan Armonk 20.08.2021 09.03.18 Gate2 105 Mohan Armonk 19.08.2021 10.08.22 Gate2 109 Sam Armonk 19.08.2021 10.03.12 Gate2 110 Sally Armonk Expected Output: Whenever the Attendance_Date >= systemtime date then it should set the flag as 0 else 1.Therefore we can filter and show records of the present date based on system date but in case of USA it is showing one day older than the system date while in Europe region it is showing correct records.How to fix this issue , please suggest a DAX? Can someone please suggest any DAX changes or any DAX forumla to handle this in a better way and to rectify this issue? Kind regards SameerConditional column/measure based on date
Hi all, I have a table with daily performance and estimate data as shown below: Date Actual Short Term Estimate Long Term Estimate 01/06/21 0.2 0.3 0.33 02/06/21 0.3 0.3 0.33 03/06/21 0.3 0.3 0.33 04/06/21 0.2 0.3 0.33 05/06/21 0.2 0.3 0.33 ... etc. Table1. Initial Table. And I need display the data in below format. Date Target Actuals/Estimate Source Jan-21 10 8 Actual Feb-21 10 9 Actual Mar-21 10 10 Actual Apr-21 10 11 Actual May-21 10 7 Actual Jun-21 10 10 Actual + Short Term Estimate Jul-21 10 9 Short Term Estimate Aug-21 10 9 Short Term Estimate Sep-21 10 9 Short Term Estimate Oct-21 10 9 Long Term Estimate Nov-21 10 9 Long Term Estimate Dec-21 10 9 Long Term Estimate Table2. Final Table I also created a 'Calendar' Table with column in MMM-YY format. I have created a measure SelectedDate = SELECTEDVALUE('Calendar'[Date]). that is filtered by slicers on the report page. I need to create a measure called "Actuals/Estimate" that will dynamically calculate based on these conditions: 1. If the date in table2 is <= SelectedDate then display Actuals Value. 2. For the dates between SelectedDate+1 and End Of Month date, Use Short Term Estimate 3. For 3 months after current months use Short Term Estimate 4. For the rest of the months, use Long Term Estimate. 5. When a selected date is September, use only Short term estimate for dates between SelectedDate+1 and End Of Year. Knowing all these conditions, I have tried to create a measure, but I could not figure it out. I could not build logic that allows to compare dates between Selected date (not static, linked to date slicer) and the dates in Table 2 to define which values I can use (Actuals or Estimates). Any advice or solution you can propose? Thanks, NurbekTimeintelligence DAX by Week previous Year not showing totals
"Income PY" using SAMEPERIODLASTYEAR shows incorrect values if showing data by week, because every year Week 1 starts on Different dates. I could fix this using the Measure "Income PY same Week" but it not shows me a Total. Anyone can help me in fixing this? I attach herbey the PowerBI-File DemoAdventureWorks for Easy Reference. Any Help arrpeciated!
