The following measure returns Month Sales: (blank)
Month Sales:=TotalMTD(Sum([Sales Amount]),'Date'[Full Date])
I have a relationship between ResellerSales-OrderDateKey and Date-DateKey
The relationship works fine when I use it in Access
I can't seem to upload images so here's the link: [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies View RelatedI want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
Say I have one factSales table with the following columns
[Sales company]
[Item]
[Date]
[Price]
And another table with valid prices per sales company
[Sales company]
[Valid from]
[Valid to]
[Price] (calculated column)
I also have a calendar table. Is it possible to get the valid price from the prices table to the calculated column in the sales table?
I have a calculated measure that is "Cost Per Patient:=sum([ActualCost]) / sum(Patients[PatientCount])" and it works fine. I'd like to be able to create another calculated measure based on "cost per patient" that uses min, max and average of "cost per patient" but dax wont let me.
I've read about dax calculate but not sure how to use it in my situation. Here is background on the problem:
There are 2 fact tables, prescription cost and patient count. Both are related by a practice dimension. The practice dimension has a geography hierarchy of county - town - practice.
The "Cost Per Patient" works fine with the geography hierarchy. By that I mean it shows the correct value when looking at the different levels. For my new calculation, for example looking at the list of counties, I want to use the min, max and average of "Cost Per Patient" of all counties. To break the problem down, I was going to start with creating and testing separate calculated measures for min, max and average and that's where I am stuck.
My understanding of the problem is it's something to do with the context of where the calculation is being made. As I want to perform the min, max and average on all the children of the parent. This article explains it but I can't figure out how to apply it to my situation [URL] ....
We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice. The example below shows the Max sales per month over the last 6 months.
Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units). Brandx should roll up to 50 units. Can DAX handle this in a single Calc Measure?
Brandx 50
Prod1 20
Prod2 30
Brandy 60
Prod5 40
Prod6 20
I have a calculated measure in Power Pivot that takes too long to run that it would max out my RAM when running it in Excel 2016 (for some reason it actually manages to run in Excel 2013 but still takes a long time). How to optimise the measure? I have shared the measure below. it is the Unique Leads (MTD) measure that I need to run in a pivot table that is broken down by everyday in a month. The Unique Leads (MTD) measure basically calculates the month-to-date unique leads where a unique lead is defined as an email address that has not appeared in the 'application' table thus far in a particular month.
Unique Leads (MTD):=[CumulativeUniqueApps]-CALCULATE([CumulativeUniqueApps], filter(allexcept('application', Affiliate[Affiliate], 'Lead Type'[Loan Type]), CALCULATE([CumulativeUniqueApps], filter('application', EARLIER([Date])< [Date]))))
CumulativeUniqueApps:=calculate(DISTINCTCOUNT([Email]), filter(allexcept('application', Affiliate[Affiliate], 'Lead Type'[Loan Type]), CALCULATE(DISTINCTCOUNT([Email]), filter('application', earlier([Date])<=[Date]))))
I have an inventory table with the following columns
[Item], [Quantity], [Date]
I have a sales table with the following columns
[Item], [Customer], [Quantity], [Date]
In addition to this I have a normal date table.
I want to create a measure that calculates
CurrentInventory:=LastInvenoryCountInTable-SalesQuantitySinceThen
I don't know where to start. Is this possible to do with DAX?
I have a sales order table that has customer, sales, product and sales date. I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer
Product
Sales Date
Sales
First Bike Date
Jon
Bike
12/4/2011
$10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Can I use function to complete? I have a datedim table as well related to the sales date.
I am trying to calculate the sum of gross sales over a rolling 6 month period in PowerPivot and don't even know how to start.
View 2 Replies View RelatedI try to calculate the turnover of an employee based on all sales on this customer and the hours spends working for this customer.To simplify, I have 2 tables.General Ledger table.I have customer no and sales.And activity table,I have customer no, hours and users.To be able to calculate the average of a customer, I made an average value based on the sales/divided by the hours on activity table.Customer 1 sales of 100$.Value of the customer is 10$/H -> John 40$ / Mike 60$.How can I use the information 10$/h to use it with the hours from a employee.
View 3 Replies View RelatedI'm working on a measure which I'd like to use in PowerView, my data model contains 2 things:
1) Employees' scores
2) Stores in which employees work in
I would like to be able to put the employee's score in comparison to the store average (which is the average of all the employee's scores in the store), and then provide every employee with an individual report on their scores against their store's score (by tiling the view in PowerView by Employee). To do this, my (first attempt) at a measure for the store average looks like the following:
Overall_Avg:=CALCULATE([Score], ALL(Employee[Name]))
Which gives me the score of the employee vs. all other employees across all stores. What I'd like to then achieve is to add an additional filter that filters only the scores for the store that the employee belongs to - which is where I'm stuck.
Here are some of my attempts at them
Store_Avg:=CALCULATE([Score], ALL(Employee[Name]), FILTER(Employee[Store] = Store[Store Name]))
returns an error for me. However, trying:
Store1_Avg:= CALCULATE([Score], ALL(Employee[Name]), FILTER(Employee[Store] = "Store 1"))
works perfectly, but doesn't give me the desired output, which is 1 measure that changes the store depending on which employee (and thus store) is being selected.
I have a requirement in Power Pivot where I need to show value based on the Dimension Column value.
If value is Selling Price then Amount Value of Selling Price from Table1 should display, if Cost Price then Cost Price Amount Should display, if it is Profit the ((SellingPrice-CostPrice)/SellingPrice) should displayMy Table Structure is
Table1:-
Table2:-
My Report Output should be look like
If tried the below option:-
1. Calculated Measure:= If(Table[Category]="CostPrice",[CostValue],If(Table1[category]="SellingPrice",[SalesValue],([SalesValue]-[CostValue]/[SalesValue])))
*[CostValue]:=Calculate(Sum(Table1[Amount]),Table1[Category]="CostPrice")
*[Sales Value]:=Calculate(Sum(Table1[Amount]),Table1[Category]="SellingPrice")
Tried this in both Calculated Column and Measure but not giving me required output.
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))
Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))
I have a table with date in the following format:
orderID | dateOrdered | customerID
101 | 01/01/2015 | 1
102 | 02/02/2015 | 2
103 | 05/06/2015 | 3
I need to create a measure that tells me how many customer's placed an order in the previous 91 days of any given date, not necessarily today.
So, if you imagine a PivotTable which has a series of dates down the left hand side and 'totalNoCustomersLast60Days' as the only column, I need each day to tell me how many customers ordered in the x number of days.
For example if we say 60 days my table would look like the following:
Each row in this table would be telling me that "x number of customers placed orders within the period 60 days prior to,and including, the date on the left".
I have tried numerous ways of doing this and despite thinking it should be incredible simple it eludes me.
I have the following scenario. I want to apply some calculations on different levels and then aggregate them up.
First measure calculates at Productgroup,color,store,size level
ProductGroup Color Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Color, Store, Size)
A Blue Store A L 100 6 600
A Red Store A S 150 4 600
A Green Store A M 160 7 1120
B Blue Store A L 300 3 900
[Code] ........
The other measure ignores color
ProductGroup Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Store, Size)
A Store A L 100 6 600
A Store A S 150 4 600
A Store A M 160 7 1120
B Store A L 640 15 9600
[Code] ...
Ignoring that gives another figure for productgroup B. In the pivot, I should see both measures at whatever attribute, except for the measure that excludes color will be null if tried split on color
ProductGroup Amount Quantity (ProductGroup, Color, Store, Size) (ProductGroup, Store, Size)
A 410 17 2320 2320
B 640 15 2820 9600
C 170 5 430 430
How should the two measure be defined:
Measure (ProductGroup,Color,Store,Size)
Measure (ProductGroup,Store,Size)
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification) .
The DAX code for calculating LY Sales is:
=
CALCULATE (
[Sales $],
FILTER (
ALL ( dCalendar ),
dCalendar[IRIYearNumber]
= VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.
Say that I have employee data for 2010-2015. I have a pivot table that displays employee data for all the different months in 2015 I want each month to show all people who have been employed since the start of time until and including that month. So
for January 2015 it should show all people employed from January 2010 to January 2015.
for February 2015 it should show all people employed from January 2010 to February 2015.
for March 2015 it should show all people employed from January 2010 to March 2015.
How can I write a measure for this? I have a date table set up.
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
=CALCULATE(COUNTROWS(incident),
apparatus[Incident Response Time] >-1 ||
apparatus[Incident Response Time] <320,
uv_901APP_TYPE[Description]="Engine",
uv_901INCIDENT[Top_Category]="Fire"
[code]....
My actual data is a bit more complicated, and uses fact and dimension tables, but I simplified it here.
I'm trying to build a chart that will compare sales of a single store to regional and national sales. The measures look like this:
Sales = SUM(FactTable[Net Sales])
Regional Sales = CALCULATE([Sales], ALL(FactTable[Store Number]))
National Sales = CALCULATE([Sales], ALL(FactTable))
And it ends up looking like this:
Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:
I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.
I have [income]- (REVENUE) table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?
account revenue revenue_date
1 100 2010-01-01
1 200 2010-02-03
1 300 2010-02-04
1 1000 2010-06-04
2 100 2010-02-01
2 200 2010-03-03
2 1300 2010-07-04
2 11000 2010-08-04
3 2100 2010-01-01
3 2200 2011-02-03
3 300 2011-02-04
3 221000 2011-06-04
4 222 2010-02-01
4 2222 2012-03-03
I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:
Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))
I have a fact table with sales data at line item level, meaning lots of rows with sales orders, revenue etc. per product group pr month. Then I have a budget table with the budgetted revenue pr product group pr month, meaning a lot less detailed. I followed the method at daxpatterns.com/budget-patterns/ with the headline "Complete pattern" to make it work. Now I have a working data model where I can see actual and budget as seperate values.
I then need to calculate the actual revenue month to date and the budgetted revenue month to date. For actual it has worked fine by doing:
=CALCULATE(
sum(Sales[Revenue]);
(DatesMTD(Time[date]))
)
The problem occurs for me doing the same on the budget figure. Since I don't have a budget per day, but only at "YearMonth" level, I have calculated the amount of workdays per month and then used the DatesMTD formula to get the cumulative workdays during the month. Furthermore I calculated the total amount of workdays in the month and made a ratio between this figure and the cumulative workdays so I get a figure in percent that tells me per day how much of the month that has went by (0-100 %). I would then multiply this percentage with the budget and get the budget month to date. This does not work since my budget figure is not at day level.
How I can get it to work. My desired result will be all the dates during a month in my rows and then actual revenue Month to date in values and a corrosponding figure for the budget.
I have created a spreadsheet using powerquery and powerpivot. Loading the data on powerquery from different spreadsheets was a charm, the problem is calculating the exchange rates per month.The structure is as following:I have 3 tables, they are:
Dates– Just a dates (loaded directly on PowerPivot).
Invoice – Details of invoices for all customers, gross totals are in different rates (loaded through power query)
Customer – Detailed information of customers (loaded directly on PowerPivot).
Currency – exchange rates (loaded directly on PowerPivot).
Note: I mentioned where they were loaded because I do not know if that really matters, I guess it does not... but extra information will not hurt. Now, let's go to how these tables are connected between each other and their key columns.The Invoice table has information on invoices dates, invoice gross total and customer code. This table is related to Customer table, which has the actual name of customers and other details. This link is done through the Customer Code column. The Currency table have a relationship with the Customer Table through the Country Column.
What I want to achieve is a formula to do the exchange rate based on the Invoice Date column present on the Invoice Table. So I have created a column on the Currency Table named at each month of the year and populated it with the exchange rates for all countries (countries listed using ISO3 format).My problem is while I am able to select a column from the Currency table to do the exchange rate calculation using the below formula, I fail to realize how to do it per month:=CALCULATE(sum(invoice[Gross Value])*SUM(Currency[Reverse]))*Reverse is the column with the exchange rate for the country money to USD.What I am failing to do is how to make that calculation to also consider the month of the invoice and pull the exchange rate for the same month from the Currency table
Get the invoice date then find the column on Currency[Month] and multiply, providing me the USD amount.I was unable to figure out a solution using the filters parameters from Calculate. If some of you ask why I am using SUM inside Calculate, is because if I only use SUM and try to do the math between the two tables I get an error saying that it was not possible to determine the value on Currency[Reverse]. Same happens with SUMX. It worked out with Calculate, but then I cannot create other formulas using CALCULATE on that table because it will give that error on redundancy, unless I add a filter on it, which worked last time (but I do not remember which thing /parameter used to filter, so do not ask it right now.I do not really need that level of accuracy, but my Virgo perfectionist side demands me to find a solution to that, that will not use a single exchange rate, but consider which is the correct exchange rate for the month of the invoice and calculate it correctly for all invoices. Just so you know, the Invoices tables has more than 1million lines.
I have an Excel database file that contains the total passenger passes from a specific location. The total number of passenger passes is counted in a period of 2 minutes(e.g. 14:45:00 to 14:46:59). I have imported my database into PowerPivot and have also created relevant PivotTables and PivotCharts with some slicers to analyze them. How can I create a slicer which filters data in greater periods of time like hour, day or month?
View 4 Replies View RelatedWe have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January)
When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)
writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',