Did you ever find an answer to your question? I have the same issue. As long as I have contiguous date the PREVIOUSDAY() works fine but weekends and holidays cause empty results. I have tried a date table without weekends and holidays and that doesn’t seem to work.
Measure for current day [Total Loans]:
=SUM(DailyLoans[CurrentPrincipalBalance])
Measure for previous day:
=[Total Loans] (PREVIOUSDAY(DailyLoans[Date]))
Date field above is from the data table. I haven’t had any luck with the Date Table. It works fine as long as all of the dates are contiguous.
You should use FIRSTNONBLANK something like
=FIRSTNONBLANK(Table1[Balance],MAX(Table1[Balance]))
Hope that helps
from Kasper de Jonge March 1, 2013 at 16:04
I posted this question in one of your blog posts but thought I would ask here too just in case:
The drill through appears to not include columns that are used to relate separate powerpivot tables which is a significant problem for me. Has anyone else seen this?
(BTW, thanks for posting a great article!)
Dale
from Dale March 4, 2013 at 16:36
The protect workbook function in the review menu hides powerpivot functions and calculations. Both the “structure” and “windows” can be protected. This is not that useful for me as protecting the structure also seems to disable the drill through feature. Has anyone else seen this issue?
Dale @Kasper de Jonge
from Dale March 4, 2013 at 16:46
Hello Kasper,
I don’t know wether I’m right asking this here, but maybe you can help me out. I’ve a “not really powerpivot” Excel file that is sitting in a Sharepoint 2013 with powerpivot (meaning that the data are not in the excel file but rather within a tabular ssas). Users who have access to the file would like to be able to save their slicers/fields selection configuration. Unfortunatelly, I cannot have the file copied locally otherwise, I loose the connection to the backend data. Would you have a way to do that ? Or can you point me somewhere?
Thanks a lot in advance,
Nick
from Nick Wälti March 6, 2013 at 02:42
Hi @Dale , protect workbook doesn’t work with PowerPivot and Pivottables. I am not aware of how to get this working.
What do you mean “loose” the connection. You should be able to connect to the tabular model from anywhere.
from Kasper de Jonge March 7, 2013 at 16:05
@Kasper de Jonge
Hi,
In normal conditions, yes, but this is prevented form a firewall. The whole is working in a citrix env. The idea is that people should work online and that works ok, but they cannot save their “preferences” that way, they can only download the file locally.
1. Defined Measures for Current year and Previous year sales using
CALCULATE (….., SAMEPERIODLASTYEAR())
2. Defined M2 measure =SUM(DimFS[ShopM2])
3. Defined M2PY measure
=CALCULATE([M2],FILTER(DimFS,CALCULATE(COUNTROWS(Sales),SAMEPERIODLASTYEAR(DimTime[RefDate]))>0))
4. Final measures are trivial (without error handling here):
SalespM2= [Sales]/[M2]
SalespM2PreviousYear = [SalesPY]/[M2PY]
from Martin March 26, 2013 at 04:12
Hi Kasper, I’m trying to come up with a calculation for calculating previous year sales with a formula similar to the following, but the calculation fails when I bring in another field (eg. product type) where there are no sales in the current year – the measure returns a blank even if there were sales in the PY as the hasonevalue(..)=0. Do you know of a workaround for this?
=if(HASONEVALUE(DateTable[CalendarYear]),
CALCULATE([Sum of SalesAmount], DateTable[CalendarYear] = VALUES(DateTable[CalendarYear]))-1, BLANK())
from jbalz March 30, 2013 at 22:12
I’m trying to sum up standard hours for multiple user id’s that also have multiple points in time, per the example below
I need to be able isolate user id’s away from PITDATE so I can get a sum of standard hours for all users – I’m not sure how to get a distinct user ID list however, in order to sum the standard hours within a powerpivot table.
something like this should work:
=if(HASONEVALUE(DateTable[CalendarYear]),
CALCULATE([Sum of SalesAmount], SAMEPERIODLASTYEAR(Date[DATECOL]))-1, BLANK())
from Kasper de Jonge April 1, 2013 at 11:28
Hi Sir,
Am a novice in Powerpivot. I am stuck in a following situation, please help.
I have a table containing records of User logged time with Login Time and Logout time. What we need is to identify total distinct users logged in hour slots.
Like if User A is logged in from 01-04-2013 10.15 AM to 1-4-2013 1.30 PM and User B is logged in from 01-04-2013 11:25 AM to 01-04-2013 01.45 PM.
So in Hour slot 10AM-11AM there is 1 user logged in and in hour slot 11AM – 12PM there is 2 users logged in and so on.
Any help would be appreciate.
Thanks in advance.
from Sachin Sharma April 2, 2013 at 00:06
Hoe kan ik in powerpivot (voor Excel 2010) dubbele records bij het samenvoegen van meerdere tabellen voorkomen? In SQL is dat geen probleem voor mij, maar in Powerpivot heb ik behoorlijk gezocht maar niet gevonden.
from Andre April 3, 2013 at 03:29
Hello Kasper,
I have an issue that I thought would be trivial, but I still can’t get it right.
I have two related tables
Dates table and related Data table (via Date)
I want to have two slicers (Year and Month) and as a result, have pivot table with the following columns:
1,2,3… 12 (months), Total Current Year, Total Previous Year
Ideally, the table should always have 14 columns (12 for months, two for CY and PY)
So, for example if I pick up month 3, and year 2013, three columns would be filled in (others would stay blank) and the aggregates would show 1-3 2013 and 1-3 2012.
I am starting to think this is beyond capabilities of powerpivot DAX and I thought of changing powerpivot into OLAP formulas, but I would lose flexibility.
Vertical axis of the table does not need any special handling, but I have problems with the horizontal column headers.
Thank you
Martin
from Martin April 4, 2013 at 04:51
@Martin
Perhaps I should add that I tried variations of this formula:
=SUMX(VALUES(DimTime[EOMDate]);CALCULATE([TurnoverFX];ALL(DimTime[Month])))
But to no avail, because the “aggregate” is still influenced by the filter context of the DimTime[Month] field in the column headers.
from Martin April 4, 2013 at 06:19
Hi Kasper, I’m trying to get Year over year customer growth on a month by month comparison. I have a Dimdate table associated to the create-date of a customer record.
I’m currently using the following calculation, but the problem is that I want to have April (or the current month) show just the YOY growth for the same time period. For example, today is april 9th 2013, so I want Last years data to show april 1st-9th 2012.
I’m trying to combine several excel files in one powerpivot. The files are downloaded from different suppliers, but all contain at least cost information per employee per period. For now I used a UNION query, but I loose all the relationship advantages this way. However to make it fully based on relations, I am lacking unique identifiers. Is there a way to join multiple tables within a powerpivot file? Or should I use a different layout to get this running?
Ik heb een dump met omzetcijfers uit ons administratiepakket via powerpivot. In de kolom Boekjaar staan alle boekjaren genoteerd ( 2010 t/m 2013 ), hierna komt de kolom Omzet waarin de omzet per boekregel uit dat jaar wordt vastgelegd. Middels een draaitabel krijg ik de boekjaren met de omzet netjes naast elkaar te zien maar wat we niet voor elkaar krijgen is hierin een extra kolom op te nemen welke het verschil in omzet tussen de twee boekjaren uitrekend ?
Hi there,
when trying to refresh the PowerPivot table I’m getting this error message
“We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”
I’m working on this for hours and could’t figure out what’s wrong.
Please help.
.
@Carlos
Carlos:
Did you ever find an answer to your question? I have the same issue. As long as I have contiguous date the PREVIOUSDAY() works fine but weekends and holidays cause empty results. I have tried a date table without weekends and holidays and that doesn’t seem to work.
Measure for current day [Total Loans]:
=SUM(DailyLoans[CurrentPrincipalBalance])
Measure for previous day:
=[Total Loans] (PREVIOUSDAY(DailyLoans[Date]))
Date field above is from the data table. I haven’t had any luck with the Date Table. It works fine as long as all of the dates are contiguous.
Charlie
removing powerpivot was a very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, very,
BAD idea
Hi @Carlos / @chall ,
You should use FIRSTNONBLANK something like
=FIRSTNONBLANK(Table1[Balance],MAX(Table1[Balance]))
Hope that helps
I posted this question in one of your blog posts but thought I would ask here too just in case:
The drill through appears to not include columns that are used to relate separate powerpivot tables which is a significant problem for me. Has anyone else seen this?
(BTW, thanks for posting a great article!)
Dale
The protect workbook function in the review menu hides powerpivot functions and calculations. Both the “structure” and “windows” can be protected. This is not that useful for me as protecting the structure also seems to disable the drill through feature. Has anyone else seen this issue?
Dale
@Kasper de Jonge
Hello Kasper,
I don’t know wether I’m right asking this here, but maybe you can help me out. I’ve a “not really powerpivot” Excel file that is sitting in a Sharepoint 2013 with powerpivot (meaning that the data are not in the excel file but rather within a tabular ssas). Users who have access to the file would like to be able to save their slicers/fields selection configuration. Unfortunatelly, I cannot have the file copied locally otherwise, I loose the connection to the backend data. Would you have a way to do that ? Or can you point me somewhere?
Thanks a lot in advance,
Nick
Hi @Dale , protect workbook doesn’t work with PowerPivot and Pivottables. I am not aware of how to get this working.
Hi @Nick Wälti ,
What do you mean “loose” the connection. You should be able to connect to the tabular model from anywhere.
@Kasper de Jonge
Hi,
In normal conditions, yes, but this is prevented form a firewall. The whole is working in a citrix env. The idea is that people should work online and that works ok, but they cannot save their “preferences” that way, they can only download the file locally.
@chall
Take a look at http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/299c66cc-366a-441a-be4f-90c48e9482a7/?prof=required for a solution
Hi Kasper, I have a trivial scenario but I still can’t get it right, I browsed yours and similar sites to no avail…
Two tables:
Sales (Date – Related to a standard time table, Sales, StoreID)
Stores (Related to Sales on StoreID, m2)
I can calculate measures forcurrent year and previous year but I still can’t get right the syntax for sales/m2.
I tried to define two measures
SumM2 = Sum(Stores[m2])
SumM2CurrentYear = SUMX(DISTINCT(Sales[StoreID]),[SumM2]))
But it does not yield the right result.
Thanks
Martin
@Martin
Errata:
In my model, I had a mess in the dimensions, which might have caused wrong aggregation.
In my second model, I seem to have solved the issue -
Tables:
DimTime
DimFS (FillingStations, “Stores”)
Sales
1. Defined Measures for Current year and Previous year sales using
CALCULATE (….., SAMEPERIODLASTYEAR())
2. Defined M2 measure =SUM(DimFS[ShopM2])
3. Defined M2PY measure
=CALCULATE([M2],FILTER(DimFS,CALCULATE(COUNTROWS(Sales),SAMEPERIODLASTYEAR(DimTime[RefDate]))>0))
4. Final measures are trivial (without error handling here):
SalespM2= [Sales]/[M2]
SalespM2PreviousYear = [SalesPY]/[M2PY]
Hi Kasper, I’m trying to come up with a calculation for calculating previous year sales with a formula similar to the following, but the calculation fails when I bring in another field (eg. product type) where there are no sales in the current year – the measure returns a blank even if there were sales in the PY as the hasonevalue(..)=0. Do you know of a workaround for this?
=if(HASONEVALUE(DateTable[CalendarYear]),
CALCULATE([Sum of SalesAmount], DateTable[CalendarYear] = VALUES(DateTable[CalendarYear]))-1, BLANK())
I’m trying to sum up standard hours for multiple user id’s that also have multiple points in time, per the example below
PITDATE USERID STANDARD HOURS
10011 04458 158.9
10011 04458 149.9
10011 05435 148.9
10012 05435 194.9
I need to be able isolate user id’s away from PITDATE so I can get a sum of standard hours for all users – I’m not sure how to get a distinct user ID list however, in order to sum the standard hours within a powerpivot table.
Hi @Daniel Maycock ,
When you want the hours per user for all pitdates you can do soemthing like:
calculate(sum([standard hours]), all(table[pitdate[))
That should do it.
Thanks @Martin for coming back to this
Hi @Rod Menken ,
Can you please file a bug at connect.microsoft.com/sqlserver
Kasper
Hi @jbalz ,
something like this should work:
=if(HASONEVALUE(DateTable[CalendarYear]),
CALCULATE([Sum of SalesAmount], SAMEPERIODLASTYEAR(Date[DATECOL]))-1, BLANK())
Hi Sir,
Am a novice in Powerpivot. I am stuck in a following situation, please help.
I have a table containing records of User logged time with Login Time and Logout time. What we need is to identify total distinct users logged in hour slots.
Like if User A is logged in from 01-04-2013 10.15 AM to 1-4-2013 1.30 PM and User B is logged in from 01-04-2013 11:25 AM to 01-04-2013 01.45 PM.
So in Hour slot 10AM-11AM there is 1 user logged in and in hour slot 11AM – 12PM there is 2 users logged in and so on.
Any help would be appreciate.
Thanks in advance.
Hoe kan ik in powerpivot (voor Excel 2010) dubbele records bij het samenvoegen van meerdere tabellen voorkomen? In SQL is dat geen probleem voor mij, maar in Powerpivot heb ik behoorlijk gezocht maar niet gevonden.
Hello Kasper,
I have an issue that I thought would be trivial, but I still can’t get it right.
I have two related tables
Dates table and related Data table (via Date)
I want to have two slicers (Year and Month) and as a result, have pivot table with the following columns:
1,2,3… 12 (months), Total Current Year, Total Previous Year
Ideally, the table should always have 14 columns (12 for months, two for CY and PY)
So, for example if I pick up month 3, and year 2013, three columns would be filled in (others would stay blank) and the aggregates would show 1-3 2013 and 1-3 2012.
I am starting to think this is beyond capabilities of powerpivot DAX and I thought of changing powerpivot into OLAP formulas, but I would lose flexibility.
Vertical axis of the table does not need any special handling, but I have problems with the horizontal column headers.
Thank you
Martin
@Martin
Perhaps I should add that I tried variations of this formula:
=SUMX(VALUES(DimTime[EOMDate]);CALCULATE([TurnoverFX];ALL(DimTime[Month])))
But to no avail, because the “aggregate” is still influenced by the filter context of the DimTime[Month] field in the column headers.
Hi Kasper, I’m trying to get Year over year customer growth on a month by month comparison. I have a Dimdate table associated to the create-date of a customer record.
I’m currently using the following calculation, but the problem is that I want to have April (or the current month) show just the YOY growth for the same time period. For example, today is april 9th 2013, so I want Last years data to show april 1st-9th 2012.
Thanks for your help!
CALCULATE([Total Leads],
DATESBETWEEN(DimDate[DateKey],
FIRSTDATE(DATEADD(DimDate[DateKey],-12,month)),
LASTDATE(DATEADD(DimDate[DateKey],-12,month))
))
Hi Kasper,
I’m trying to combine several excel files in one powerpivot. The files are downloaded from different suppliers, but all contain at least cost information per employee per period. For now I used a UNION query, but I loose all the relationship advantages this way. However to make it fully based on relations, I am lacking unique identifiers. Is there a way to join multiple tables within a powerpivot file? Or should I use a different layout to get this running?
Thanks for your help!
Beste Kasper,
Ik heb een dump met omzetcijfers uit ons administratiepakket via powerpivot. In de kolom Boekjaar staan alle boekjaren genoteerd ( 2010 t/m 2013 ), hierna komt de kolom Omzet waarin de omzet per boekregel uit dat jaar wordt vastgelegd. Middels een draaitabel krijg ik de boekjaren met de omzet netjes naast elkaar te zien maar wat we niet voor elkaar krijgen is hierin een extra kolom op te nemen welke het verschil in omzet tussen de twee boekjaren uitrekend ?
heb jij hier een oplossing voor ?
alvast bedankt voor je hulp !
mvg Jelte
Hi there,
when trying to refresh the PowerPivot table I’m getting this error message
“We couldn’t refresh the connection. Please go to existing connections and verify they connect to the file or server.”
I’m working on this for hours and could’t figure out what’s wrong.
Please help.
Joe
Hoi Jelte,
Ik zou je aanraden om je wat te verdiepen in het maken van measures. Enekel boeken die je daarbij kunnen helpen:
http://www.pearson.nl/produkt.asp?isbn=9789043027366&cat=com
Of Rob Collies boek.
Groeten,
Kasper
Hi Robert-Jan,
You can use Data Explorer to merge different files into one table.
Hope that helps,
Kasper