power pivot

I have a Dimension Table and a Fact Table brought in by Power Query. In Power Query, I added a Calculated Column to each table named "Report_ID". I'm trying to create a relationship in Power Pivot for these tables, but I keep getting the error message that Power Pivot does not currently support...

HI All, I am trying to create a calculated column using an if/and DAX statement in PowerPivot and for some reason I keep getting errors so I am not sure if its the formula or if I am completely unable to perform what I want. My formula is below, can someone please help =IF(([Actual Order...

I need a DAX measure that compares latest version of financial data against prior version My data table has an'asof' column to tag which version the data originates from (100K+ rows) For example, I would like to compare the full yr 2019 cash number asof Sept 2019 vs. the full year 2019 cash...

Hi, I have a DAX formula for MTD I'm looking to total my sales YOY using QTD. So today is the 12 of September I need to see all sales QTD ending today and will compare them year over year. This is my MTD formula: =if(month(dCalendar[dateS])=month(today()) &&...

Hello all, I am in desperate need of some help with rolling back our inventory and seeing what the discount amount was on a prior date. I have a measure that rolls back the date and I have a table with the historical discounts by date. What I need to see is: SKU QTY DISCOUNT AMOUNT VALUE...

Hello all, I'm looking for a way to refresh a DAX measure not the whole workbook. When I change a value in a cell. So if I change let's say the date in a cell I want a specific measure to be updated. Otherwise refreshing the whole workbook takes may staff 20 minutes. When I can go direct to...

I am trying to have texts in a value field in a pivot table, and my understanding is that creating a Measure (concatenating the text) is the best way to do so. However, I am unable to create a measure when I am in the Pivot Table, as even after I select "Add Data to Data Model" when creating the...

Hello, I only started using PowerPivot after Power Query was readily available. I come across example Excel/PowerPivot documents where the data is embedded into the Data Model and I don't understand how you can modify the connections. I found this cool PowerPivot example here on the forum...

From what I recall every workbook I opened had this connection whether it had a data model or not At any rate I cannot see it in ANY of my workbooks. The data models are there, but none of them have that anymore I used to use it to increase the number of rows I could drill down on. Any...

Hi I have 2 payroll tables. MAIN and MGMT. They both don't contain the same fields, but do have common fields such as name, date rate... I currently paste the data from our payroll export into 2 tabs and pull them into power pivot and back down as pivot tables. But I would also like to...

I set up external connections to excel workbooks to load data into power query (went fine). I decide to test the refresh feature by changing some things in the source data. It won't refresh -ever. Any idea what he problem is. What's the point of having a refresh feature if the connection...

Hello Everyone, I would greatly appreciate any help anyone may have on this problem, I have combed the internet looking for an answer but have only slightly gotten close so here we go.... Is there anyway possible to retrieve data from the power pivot data model that has been loaded from power...

Hi all, I have been hunting for an answer to this for days and just getting nowhere :eeek: We have a table of rental service charges that have 4 key Fields; Start Date End Date Quantity Sell Price I have created a Date (calendar) Table and created a relationship to both the Start Date &...

I have a date table that is standard. In my data table I have a list of 'ClosedDates' that does include blanks (maybe this is the issue?). When I filter this table manually I get the results I expect, a count of 380 rows. However, my measure is only calculating 363 rows. I went through the data...

I'm a beginner with Power Pivot/DAX, and got thinking about that: Given two tables: items and groups, related by columns [groupid]. I create calculated columns in the groups table. There is more than one way to do it: groups[n items 1]=COUNTROWS(RELATEDTABLE(items)) groups[max fact...

Hello, I have many measures (f.e. GDP:=sum([total rev])...) and I want these measures to have assign a characteristic (f.e. CF, or PL, or BS). Is there a way to map a measure to a characteristic in a data model? Thank you in advance

I am attempting to become more efficient with a process I do monthly by utilizing Power Pivot (which is new to me) verse excel and multiple formulas. All of my data is in one table and I'm attempting to replicate the formulas that have been created for use in excel for use in Power Pivot...

Hi all I have a challenge in excel that I cant seem to solve. I built i fairly straight forward financial reporting model using power pivot. Some data are fetched from Dynamics NAV, some from within excel. All data is returned to the data model using queries. I have set up a report in excel...

I want to add calculated subtotals into a pivot table Like in a finicial report you want to se Margins after different categories I have the scenario in the tabels below. My datatable is added to the datamodel with connection only. A table with balance per account, I have created an "Income...

When I go into Power Pivot in Excel 2016 and Excel 2019 and select Get External Data From Other Sources I get nothing, no Table Import Wizard. Any ideals why? Thank you!