Compare dates in two columns and then sum 3rd column
I am using Power Pivot in EXCEL not Power BI. Trying to compare dates with an if statement from two columns and when the date matches I want to sum a 3rd columns. Here is the DAX formula I have, which is not working. What do I need to change to be able to compare the dates and then sum up the values? I also created date tables for the two date columns. Didn't work either. MM CM:=IF(VALUE('Vendor Trend'[CAP Date Paid])=VALUE('Vendor Trend'[CAP Date Aff]), SUM([TOTAL MM])) MM CM:=IF(VALUE('Calendar'[Date])=VALUE('Calendar_Dt Aff'[Date]), SUM([TOTAL MM]))
Help with Running Calculation - DAX
Hi everyone, I'm hoping someone can help me with a running calculation that I’ve been struggling to get working. I’ve attached a link to the https://docs.google.com/spreadsheets/d/1yNDOkxsRPuLA_zxsdgapJm2jMqog9Fnf/edit?usp=drive_link&ouid=102505936425969572198&rtpof=true&sd=true spreadsheet with the relevant data, and below is a snapshot of what I’m trying to achieve. We work with a 13-period forecast, and in the data, I have a column for periods 1 to 13 and a column for EI $ which is calculated as EI % times the period salary+period bonus. However, there is a cap for the total EI amount for the year, which is shown in the EI Max Amount. In the example provided, the EI $ would nearly reach the max by period 3, and in period 4, the amount would only be the remaining balance to meet the yearly cap. After the total EI cap is reached, all subsequent periods should display zero to ensure the forecast is accurate. I really appreciate any help you can provide in getting this to work properly. Thank you in advance! Department Type of Employment Shared Level Bonus % Status Expense Annual Salary Adjusted Salary Period Toggle Period Salary Team Factor Indivial Factor Period Bonus EI % Calculated EI $ Calculate EI MAX Amount Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 1 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 2 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 3 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 4 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 5 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 6 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 7 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 8 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 9 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 10 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 11 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 12 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24 Customer Permanent No 13 30% Active Customer $211,150.00 $211,150.00 13 1 $16,242.31 75.00% 120.00% $4,385.42 2.32% $478.56 $1,466.24
Running Total per week/month
Hello, I would like to have a measure in power pivot for the running total that behaves like the one in the normal pivot tables. For instance, in the pivot table below the value are shown as running totals based on the week number (y axis). The running total is based on the week number, and for example after w6 in January it`s still reported the maximum value for the month. Here it's the most i managed to accomplish via power pivot: Here the code for the measure: ActualsRunningTotal:=TOTALYTD(SUM(Actuals[Volume]), Actuals[Date], ALL(Actuals)) Here the structure of the model, highlighted the fields i use in the pivot. I didn`t quite manage to find online how to handle this case, any ideas? Should i adjust the relationship between tables or use different functions? I`m doing it via power pivot because then i would need to combine the measure with other two, for which I only need the normal totals. Thank you in advance, any help is very much appreciated KR
How to display Grand Total correctly with USERELATIONSHIP?
Good day, I’m having trouble with getting the correct Grand Total for my pivot table. I have two tables. Fact table of email worked by an employee. It has a pkey column which is just a concatenation of week and agentId. Dimension table that has the login data of employee. It has an active citrixKey connected to Fact table.In general, Fact.agentId is the same as Dimension.citrixUsername, however an employee encountered issues with the citrixUsername and is now using Dimension.ntLogin. When I tried to build a pivot table with rows as agentId and values as sum of values, the grand total is not correct. Here are my measures: sumEmails:= SUM( 'Fact'[Value] ) calcEmails:= IF( [sumEmails] = BLANK(), CALCULATE( [sumEmails], USERELATIONSHIP('Fact'[pkey], 'Dimension'[ntloginKey]) ), [sumEmails] ) SampleNeed to count the number of times a name appears in a column based on a separate list of names.
I am working with a dataset that has various tasks that need to be completed on a single invoice. I need to create a pivot table that counts the number of times each employee's name shows under each task. However the data set has names of people (i.e. Erin) that I don't need to include in the pivot table. The only names I need in the pivot table are shown under the "employee" table (a.k.a. Table 2). I'm trying to make this sheet easy to work with so as new employees come along we can simply add their name to the "employee list" and it will add them into the pivot table. I have tried countrows, counta, and other various measures in my data model but have not been able to get it to work yet. With not being able to create relationships between the two tables it is makeing it difficult to filter how I need it to show. The closest I can get only takes one column's data into account and the will then duplicate that into the next columns reguardless of the field I select to be in the next column. Here is a screenshot of a sample data sheet I put together to illustrate the problems I am having. I have also created a manual caluclation at the bottom to illistrate how I would like the data to show.
RELATED doesn't work in Many-to-One relationship in Power Pivot
Hi I'm using Power Pivot in Excel with a many-to-one relationship. I want to get data from the one side to the many side. I trie calculated column and measure to do so but all failed. The calculated column returned blank and the measure showed error. DAX for the calculated column and measure are the same as below. =RELATED('BH_Capacity_Standard'[Max SKU Capacity]) I remembered I have used similar measure in Power BI and I think there's no big difference between Power BI and Power Pivot about DAX. Thanks in advance!
Need help on correcting pivot table total-dax/power pivot
I am new to Dax and power pivot and need help with a measure. From the Table below, I have managed to create a measure for finding the max of sum of sales by product and Name. =maxx(SUMMARIZE(Table1,Table1[Name],Table1[Product],"Sum",[Sum of Sales]),[Sum]). But I could not correct the total of max sales. The correct sum on the pivot should be 22,000, not 12,000. Can you please suggest the correct Dax measure to achieve this? I could not correct this through Hasonfilter. Pivot: Table: Name Product Sales Anil AA 7000 Anil BB 7000 Anil BB 4000 Anil AA 3000 Anil AA 2000 Sunil AA 6000 Sunil BB 7000 Sunil AA 4000 Sunil BB 3000
