dax

Hi When trying to find the maximum value with a filter condition is there any difference between these two methods and which if either is regarded as better; ; MAX( Table1[Units] ) Then, CALCULATE([MaxU],(FILTER(ALL(Table1[Item]) ,Table1[Item]= "A") ) or...

Hi, Does anyone know how to do a cumulative total for a column not measure and not using "EARLIER', so the same result as CALCULATE([Tunits], FILTER(ALL(Table13),Table13[Date] <=EARLIER(Table13[Date]))) I've tried using CALCULATE( MAX( Table[Date] )) ...

Hey guys! I use this VBA since 2018, but now I want to use it in Power BI. Can someone please help me converting this from VBA to DAX, so I can use it? The VBA calculate the period between the two dates, only considering working hours (8h30-18h30), working days, discarding weekends and holidays...

Hi, I've managed to make a dynamic ranking with a custom TopN filter, output is a table with brands ranked by TopN filter and the rest is always summed up by a total under "Others". This is because there are several subcategories. So within a subcategory the dynamic filter is working on...

Hi I am building a dashboard for survey data and for reasons I am not quite sure I am running into a couple of problems. I have five questions that I want to focus on. My dashboard looks like so: 1) I wrapped the text box at the top with a blank box that i removed the borders. When I...

Hi All, I am trying to do the following, please let me know if this is possible: I have a "Value Field" called Count of Value_Proficiency which simply counts the occurrence of each Row Label I would like to create a new "Measure" having Max of Count of Value_Proficiency repeated for all Row...

I am struggling with DAX Measure to Countif on Measure Result in condition. Count of Items whose result of Measure4 = 0 for the selected Sales Centre and Month (YearMonthNumber). I am trying with this measure =CALCULATE (COUNTA (Data [Item Name]), FILTER(ALL('Data'), [QualificationUpto20] = 0))...

This is cross posted with Excel Forum; DAX Using a Measure in formula produces a blank and can't see why My question relates to the following formula to get the previous value from non consec dates; first I have a filtered down table. Filtered Dates := MAXX ( FILTER ( ALL (...

Hello Everyone, I have this dataset (click here) of items with time stamped stages. Some stages mean the item is occupying space in the storage. The other stages mean it's not. I need to use Power Pivot 2016 (not Power BI) to produce the chart below for avg. storage occupancy over time for the...

I'm trying to create a measure which will incrementally subtract demand from supply based on rank. The ranks are already assigned and exist in the data (e.g., 0-100). Is there a way to do that in Power BI? My target end state is a matrix visual that shows each product and it's rank on the...

Hi, I'm trying to create a Excel DAX calculated column for the dates next week only. This works for THIS week only. =if( year(dCalendar[wtd])=year(today())&&weeknum(dCalendar[wtd])=WEEKNUM(today()-1)&& (dCalendar[wtd])<=TODAY()-1 ,"CURRENT WEEK","OTHER") But this doesn't for next week. =if(...

My organization has a report of the cumulative net income for each quarter for each year. I need produce a report in Power BI that pulls the quarterly net income rather than the cumulative net income. I currently have three DAX measures, one that pulls the current cumulative net income, a...

Hi, Can someone tell me if Powe pivot and Power Bi work differently in relation to date / Calendar Tables, So if I have a meaure ; SUM(TslaesTable[Sales} ) and I use this in a pivot table which is just has Dates in the Rows, if i use the dates from the sales table I get what I would...

Hi I've created a running total column by date and criteria, first I used ALLEXCEPT , but then ALL and VALUES so; RTColumn := CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), Table1[Date] <= EARLIER ( Table1[Date] ) ), CALCULATETABLE ( VALUES ( Table1[Agency] ) ) )...

Hello, I was looking at different ways of dealing with a running total and I wondered if anyone had any advice on benefits of problems with different methods with regards to efficiency on large data sets which I don't deal with but it would be good to know. RTM := VAR mdate = MAX (...

Every week I run a report from our HRIS and drop a CSV file into a folder that is connected to Excel via PQ. I use the cleaned-up data to "feed" pivot tables and use them in a dashboard. I have no problem getting a current headcount, but I would like to be able to get a headcount for each month...

Hi, I'm trying to add the day of the year number in a power bi calendar , and keep seeing the following; DATEDIFF (STARTOFYEAR ('Date' [Date]),'Date' [Date],DAY)+1 I have tried using this and just get errors, "Day of Month", DAY([Date]) , "Day of Week", WEEKDAY([Date] ,2) ...

Hi, I have a question re variables, which i tend to use as it makes it easier to understand but in the formula I have for a running total in a calculated column I have to use a var or it won't work; VAR aitem = AllandValues[item] RETURN CALCULATE ( [avsales], FILTER (...

Hi, I've been doing a bit of experimenting, so I have a simple measure ; SUM(Tsales [Units] ) Whichi I have then wrapped in a AVERAGEX and SUMX , its only a two column table Item and unit and I made sure I had duplicate values, which as it's tiny it is easy to see how the duplicates are...

Is there a way of putting the date column into a matrix that is not a hierarchy, I can do this using the date column from the fact table, but would prefer to use calendar table as I've gone to the trouble of making it, essentially the left , which uses the calendar table to look a bit more like...