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]))Measure that filters a table to a single row and returns text from a specified column (Excel365)
Hi there I still have a lot to learn about DAX, and have become stuck trying to develop a measure that will filter down my source table to a single row by using values in two columns and return the (text) value from a different, specified column. I have two measures that filter a table and calculate the expected values for (a) the oldest date for an unresolved service ticket and (b) the corresponding number of days elapsed after that date, but what I am unable to do is to retrieve the text value from a column for the row that table is calculating the age and date from. I've been going around and around in circles researching and trying different functions for far too long and am super confised now. I would really appreciate some help! 🙂 Most of my attempts at developing a measure result with an error message advising ~ that multiple have been supplied where a single value was expected, but by now I have failed in dozens of different ways. I've knocked up a file with dummy data and measures for reference - hope this helps with understanding my request and developing a solution. The pivot table I'm working with is used to generate statistics that are consolidated and presented as part of a dynamic and interactive 'dashboard' on another sheet; it is the "SYS" values that I am trying to populate with this measure: The image below is from the sample file provided, with the column in pink manually created to illustrate the desired result. Thanks in advance for your help!🤗
How to write a measure that counts rows from 2 different dates columns in Power BI
I need to create a measure that counts rows based on 2 different date columns. Using 10/1/2023 as an reference example this should return 4 records. Where the Work Date is <= 10/1/2023 and the Paid Date is >= 10/1/2023 If I used 11/1/2023 it should return 5 records. How would I translate this to DAX? In Excel this is easily accomplished by adding a filter and then entering "Custom Date" for both Date columns. Date Filters in Excel : For example in excel the custom filter options: Work Date is before or equal to 10/1/2023 and Paid Date is after or equal to 10/1/2023 Adding a Custom Filter Excel : The data model is using both active and inactive relationships. How would I translate this to DAX? Using 10/1/2023 as an reference example this should return 4 records. Where the Work Date is <= 10/1/2023 and the Paid Date is >= 10/1/2023 If I used 11/1/2023 it should return 5 records. Test Data : OrderDate OrderEndMonth Forks Napkins Work Date Paid Date 2/13/2023 2/1/2023 1 0 2/23/2023 2/1/2023 1 0 3/2/2023 3/1/2023 1 0 3/1/2023 12/6/2023 10/22/2019 10/1/2019 1 0 10/1/2019 11/26/2023 10/29/2019 10/1/2019 1 0 12/23/2019 12/1/2019 1 0 12/1/2019 10/22/2020 4/4/2023 4/1/2023 1 0 4/18/2023 4/1/2023 1 5 4/1/2023 6/22/2023 3/26/2019 3/1/2019 1 3 4/1/2019 8/30/2019 4/30/2019 4/1/2019 1 0 5/28/2019 5/1/2019 1 0 6/4/2019 6/1/2019 1 1 6/1/2019 7/15/2023 10/31/2023 10/1/2023 1 1 11/1/2023 12/14/2023 7/9/2019 7/1/2019 1 4 7/1/2019 8/21/2019 7/31/2018 7/1/2018 1 2 8/1/2018 10/10/2018 3/13/2018 3/1/2018 1 1 7/23/2019 7/1/2019 1 0 8/1/2019 9/24/2019 10/24/2019 10/1/2019 1 0 11/1/2019 12/11/2023 10/17/2019 10/1/2019 1 0 11/25/2019 11/1/2019 1 0 12/3/2019 12/1/2019 1 1 12/1/2019 11/15/2023 1/7/2020 1/1/2020 1 7 2/1/2020 2/28/2020 1/2/2020 1/1/2020 1 2 1/1/2020 2/18/2020 Expected Values: Dates Values 1/1/2023 4 2/1/2023 4 3/1/2023 5 4/1/2023 6 5/1/2023 6 6/1/2023 6 7/1/2023 5 8/1/2023 4 9/1/2023 4 10/1/2023 4 11/1/2023 5 12/1/2022 3 Here are the measures I have tried so far, the closest one is TestD. However, when i get to June of 2023 it returns the wrong count of June (5) should be 6 and October (5) should be 5. Test1 = CALCULATE( COUNTROWS('TestData') ) TestA = CALCULATE( SUMX ( 'Date', CALCULATE ( COUNTROWS ( 'TestData' ), FILTER ( 'TestData', [Date] <= 'TestData'[Work Date] && [Date] <= 'TestData'[Paid Date] ) ) ) ) TestB = CALCULATE( COUNTROWS( FILTER('TestData', 'TestData'[Work Date] <= MAX('Date'[Date]) ) ) ) TestC = CALCULATE( COUNTROWS('TestData') ,'TestData'[Work Date] <= MAX('Date'[Date]) && 'TestData'[Paid Date] >= MAX('Date'[Date]) ) TestD = CALCULATE( COUNTROWS( FILTER( ALL('TestData'), 'TestData'[Work Date] <= MAX('Date'[Date]) && 'TestData'[Paid Date] >= MAX('Date'[Date]) ) ) ) TestG = COUNTROWS( FILTER( 'TestData', 'TestData'[Work Date] >= MIN('Date'[Date]) // date1 must be in the current month 1/2 && 'TestData'[Work Date]<= MAX('Date'[Date]) // date1 must be in the current month 2/2 && 'TestData'[Paid Date] >= MIN('Date'[Date]) // date 2 must be in the current month 1/2 && 'TestData'[Paid Date] <= MAX('Date'[Date] // date2 must be in the current month 2/2 ))) I'm really stuck on this one and have tried many things for a couple of days now. Any help would be greatly appreciated. Added the Dropbox link to the pbix here SampleTestDates.PBIX
Formula To Excel
Pease give DAX for =-SUMIF(Details!B:B;'Cash Flow'!G34;Details!G:G)+4801. I have DebtorMovements = CALCULATE( -SUM('ZTBR'[Amount in USD]), 'ZTBR'[Roll_Up_Function] IN {"Debtor Movements"} ) + 4801 Is this correct? It gives me the value, but that same value then displays for all the rows, which should not be.Power Pivot - Calculated Column - How to find the 2nd minimum value based on other column groups
I'm having trouble creating a Calculate Column in Power Pivot. I have a table that shows bid results from multiple suppliers for multiple items and divisions. Through research I've been able to figure out the DAX formula for the minimum bid (by Division, by Item). =CALCULATE(MIN(Table1[Bid Cost]),ALLEXCEPT(Table1,Table1[WAREHOUSE / DIVISION],Table1[ITEM NAME])) I was also able to figure out the percent difference based on the lowest bid. =(Table1[Bid Cost]-CALCULATE(MIN(Table1[Bid Cost]),ALLEXCEPT(Table1,Table1[WAREHOUSE / DIVISION],Table1[ITEM NAME])))/Table1[Bid Cost] I'm now being asked if I can do the same for the 2nd lowest bid (by Division, by Item) to place in a Pivot Table for when we make negotiation calls. I've been looking for a couple days through the forum and google, and cannot seem to find an answer for the 2nd lowest value Calculated Column. In Excel, the formula would be: 2nd Lowest Bid =SMALL($D$5:$D$13,2) % From 2nd Lowest Bid =(D5-SMALL($D$5:$D$13,2))/D5 Here is my data sample. I apologize, I'm trying to figure out how to attached the sample data file, but my OneDrive and GooglDrive is blocked by IT security.Measure for powerpivot to create average of utilization of total population of different field
Hi All - I am hoping someone can help me with a question rather quickly. I'm trying to calculate the average of the Sum of "Total Units" in an entire population. My current measure formula is only displaying the average of total units of the total population at the CPT Code level opposed to the average units of the CPT Code at the entire population level (field "Claim Number"). The challenge that exists is there could be "Claim Numbers" that do not contain specific CPTs. However, I want the calculation of the "average units" of the CPT utilization to take into account the ENTIRE population so the average is representative of the entire population. I'm not well versed at editing tables outside of excel, so my preference is to use a measure formula. However, if this cannot be achieved be creating a powerpivot measure I am open to trying to trying something through the "manage" Data Model. The other option is to paste all CPTs for all claim numbers with a unit frequency of zero, but that would not be efficient for future updates and also likely get into the millions of rows scenarios. Current Measure Formula '=DIVIDE(Sum('Range 1'[Units Billed]),DISTINCTCOUNT('Range 1'[Claim Number])) Example current output (incorrect as it is only calculating the average utilization of the total of CPT units (97530) Example Pivot Structure Appreciate the help and consideration! Thank you, Sam
How do I create a measure to sum (total) up a distinct count?
Hi guys, As you can see in the picture down below I created a table with different colums: First column [VullingTotaalPerc] shows the percentage of version ID that are filled 100% and so on Second column [Count of Versies.Id] shows the count (distinct) of the total version ID. Third column [TotalVersiesIDCalculate] is a measure that I created that shows the count (distinct) of the total version ID but only for department X. Measure = TotalVersiesidCalculate = calculate(DISTINCTCOUNT(MKA[Versies.Id]),MKA[ProductieAfdeling]="COC2 en I&V") Fourth column [DistinccountVersies.id-Calculated] is a measure that I created that show the count (distinct) of versies.ID. DistinccountVersies.id-Calculated = DISTINCTCOUNT(MKA[Versies.Id]) Fifth column [COC2-Calculated] is a measure that I created - [TotalVersiesIDCalculate] divided by [DistinccountVersies.id-Calculated] to show what the percentage is of the total amount. The problem now is that when I use this formula: [TotalVersiesIDCalculate] / [DistinccountVersies.id-Calculated] the measure will divide 8 with 56 but what I want is that 8 will be divided by the total of 2533. Can anyone help me out here? Thnx!
