PowerPivot and DAX papers,posts,FAQs,webcasts,samples

Experimenting with a few functions. I have a basic dashboard, 4 charts showing YTD Sales split amongst 4 groups, YTD Sales by Rep and by location.
I have slicers for year and even month.

When I put in the ParallelPeriod I'm getting the right total sales amount for the same period last year. However, my first chart is split amongs major product groups showing current sales & the total sales last year but no delineation amongst those groups.

I don't have the syntax handy (pulling from memory) but it's calculation(sumx(Sales(Sales),parallelperiod(Sales(InvoiceDate),-12,month))
not putting in all those lovely quotes, dashes, etc.

Do I need to set up parallelperiod for each product group, each sales rep. individually, or is there some syntax I'm missing to have the result automatically filter into their respective graphs to match their focus?

Hi Mongo,

I think your syntax isn't complete, i guess it would be :
=calculation(sumx(Sales(Sales),parallelperiod(Sales(InvoiceDate),-12,month),all(Sales))
without the all() the time intel. function won't work. But the All() function will also make sure you cannot slice/show data by something other than dates.

You should create a separate time table, create a relationship on InvoiceDate and use the data from the new table in the functions:
=calculation(sumx(Sales(Sales),parallelperiod(DateTable(Date),-12,month),all(DateTable))

I think this will solve your problem. I'm currently writing a blog post explaining this in detail.

Kasper de Jonge

oops. Yes. Like I said, I was pulling it from memory. I can confirm that I did put in All().
Here's another problem. My sales invoice dates are in the same table as my sales. If I put in All('Sales'[InvoiceDate]) I get - nothing, even though the syntax reads Table_OR_Column. But if I put in table: Sales, I did get my totals. Again the result is still the same, total sales last period regardless of graph/table focus.

Hi Mongo,

I have created a blog post explaining how time intelligent functions work:
business-intelligence.kdejonge.net/power...ow-to-work-around-it

I think this will answer your questions. You have to use the ALL and not just ALL with only the time column, although allexcept(Sales,Sales[Product], Sales[othercolumn]) probably will work.

Kasper de Jonge