Hi all,
I have a question on Powerpivot's SAMEPERIODLASTYEAR DAX function. On the surface the formula works perfectly..... however, on closer inspection there appears to be a bug.
I am working with a dataset that contains information on customers from a number of stores.
1) When I first drag the Store field onto the rows section I am given a complete list of ALL the stores in the dataset. (As expected)
2) I then drag customer count to the data section. (The rows still show all stores and their total customer counts - again as expected)
3) I then add a month field to the report filter (I'll refer to this as Current_Month). The list of stores now reduces to only those stores with customers in the Current_Month. (Still as expected)...
this is where it starts getting annoying....
4)
I add a new measure as follows PCP_Customers:=CALCULATE(SUM(QUERY[Customers]),SAMEPERIODLASTYEAR(Query[Month]))
This formula works beautifully....except.....I only have the rows showing up for stores with records in Current_Month. Each row is showing the correct PCP_Customers and the total is correct but there are certain stores with customers in the PCP (Prior Corresponding Period) month that are not showing up. The overall total is correct as well - so somehow the total of PCP_Customers is not the same as the sum of the individual rows.
Has anyone dealt with this before? I have had problems before with SAMEPERIODLASTYEAR with February and the leap year but I found a workaround for that by changing the date to something other than the end of the month.
Let me know if this isn't clear & thanks in advance
I have a question on Powerpivot's SAMEPERIODLASTYEAR DAX function. On the surface the formula works perfectly..... however, on closer inspection there appears to be a bug.
I am working with a dataset that contains information on customers from a number of stores.
1) When I first drag the Store field onto the rows section I am given a complete list of ALL the stores in the dataset. (As expected)
2) I then drag customer count to the data section. (The rows still show all stores and their total customer counts - again as expected)
3) I then add a month field to the report filter (I'll refer to this as Current_Month). The list of stores now reduces to only those stores with customers in the Current_Month. (Still as expected)...
this is where it starts getting annoying....
4)
I add a new measure as follows PCP_Customers:=CALCULATE(SUM(QUERY[Customers]),SAMEPERIODLASTYEAR(Query[Month]))This formula works beautifully....except.....I only have the rows showing up for stores with records in Current_Month. Each row is showing the correct PCP_Customers and the total is correct but there are certain stores with customers in the PCP (Prior Corresponding Period) month that are not showing up. The overall total is correct as well - so somehow the total of PCP_Customers is not the same as the sum of the individual rows.
Has anyone dealt with this before? I have had problems before with SAMEPERIODLASTYEAR with February and the leap year but I found a workaround for that by changing the date to something other than the end of the month.
Let me know if this isn't clear & thanks in advance
