time intellignce and slicers

Anything related to PowerPivot and DAX Formuale
PeterD
Posts: 3
Joined: Fri Jun 09, 2017 9:32 pm

time intellignce and slicers

Postby PeterD » Fri Jun 09, 2017 9:51 pm

Hallo,
I have created a calendar table with every day since 2009 and it is mark as a date table
I have a customer table with customer number.
I have a production table with customer number, production date and quantity.(Quantity Fieldname='ARBG'[G_A]))
The customer table and production table are linked via the customer number.
The production table and the calendar table are link via the date field.

I have a customer with production quantities in this and the previous year.
The production is in the same month but not on the same day in the previous year. (i.e. 14.01.2017 and 13.01.2016)

I want to use a slicer to show quantity of actual year from year 2017 and month 1 to 4.
I want to use a function to calculate the quantity of the previous year.
I have tried sameperiodlastyear. I have experimented with firstdate and lastdate and dateadd minus 12 months.
All to no avail.
For customers where the Dates of the actual and previous year are not identical, no quantities are shown.
for example
=calculate(
sum('ARBG'[G_A]);SAMEPERIODLASTYEAR('MA_Date'[Full Date]))

Even if I explicitly write the Dates in, it doesn't work.
=calculate(
([G_A (Ist)]); DATESBETWEEN('MA_Date'[Full Date];
"01.01.2016";
"30.04.2016"))
My file gives me the hint, that relationships between the tables may be necessary.
But the relationships are there.
Please help.

MattAllington
Posts: 836
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: time intellignce and slicers

Postby MattAllington » Fri Jun 09, 2017 11:31 pm

When ever doing time intelligence, you must use the date column from the date table when asked for "dates". It looks to me like you are using the manufacture table date column Instead.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

PeterD
Posts: 3
Joined: Fri Jun 09, 2017 9:32 pm

Re: time intellignce and slicers

Postby PeterD » Fri Jun 09, 2017 11:34 pm

Hi Matt,
I am using the date from the data table. The table MA_Date is the date table not the production table.
Thanks all the same.

MattAllington
Posts: 836
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: time intellignce and slicers

Postby MattAllington » Sat Jun 10, 2017 11:17 am

Well there is nothing wrong with the formulas, but clearly there is something wrong. Can you reproduce it in a sample workbook that you can post
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

PeterD
Posts: 3
Joined: Fri Jun 09, 2017 9:32 pm

Re: time intellignce and slicers

Postby PeterD » Tue Jun 13, 2017 1:50 am

Sometimes (even if you are new to DAX) you have to have faith in your formulas. :D
The mistake was in the data.
Data corrected, formula delivers the expected result.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest