Issues Dealing with Time Intelligence

DAX DAX Calculations
1

I need help.
I don’t know what I am doing wrong. My model looks nice and simple but my measures which should be simple enough is not right.
I have computed simple measures like:

Avg Added to WL Per Week =

AVERAGEX(
VALUES( ‘Date’[Week & Year] ),
[Total Manufactured]
)

Addition to Manufactured Line Per Week =

SUMX (
VALUES( ‘Date’[Week & Year] ),
[Total Manufactured]
)

to return the average per week but result not accurate and likewise some others

I have to date columns in the Product Details table and therefore using USERELATIONSHIP to get values for manufactured date data,

Total Manufactured = CALCULATE( COUNTROWS( 'Products Details'),

USERELATIONSHIP(‘Date’[Date], ‘Products Details’[Manufactured_Date] ))

Is my model wrong? What is wrong with my queries?
I appreciate your replies.

Product_Demo.pbix (233.1 KB)

2

have you checked out all the date time intelligence in the education portal?

there are video that would take you through it all.

thanks
Keith

3

Thanks Keith,
I did. That is were I got these from

AVERAGEX(

VALUES( ‘Date’[Week & Year] ),
[Total Manufactured]
)

It is weird to me that I am not getting the desired results
Thank you

4

HI,
Can you provide few expected results.
It will help me to look exactly where is the problem.

Thanks,
Anurag

5

@upwardD ,

I agree with @Anurag that seeing the mockup of the desired results will be very helpful in crafting a solution.

Your data model looks completely solid. The problem is in your measures – what you’ve actually done is rewritten the same measure three different ways, since the VALUES function will respect the filter context in the visual, while ALL will not. To demonstrate this, in your measures try replacing VALUES with ALL – they will generate the same virtual table, but behave very differently with regard to the filter context in the visual, and thus will return very different results.

If this is not clear to you, I would suggest watching the recording of Power BI Accelerator Week #3, where @sam.mckay and I go through these filter context concepts in detail.

I hope this is helpful.

  • Brian
6

Thank you @Anurag and @BrianJ.
Appreciate the replies.

The expected result will be obvious to me when the data returned per month, per year are different.

I have tried replacing VALUES with the ALL function but the results are still weird.

image
image1589×636 54.8 KB

I anticipate your replies, please.
Thank you

7

@upwardD ,

Just so we’re not playing a back and forth guessing game, can you please mock up the results you are expecting in each column in Excel? Then we can be sure that the solution @Anurag or I provide will meet your requirements.

Thanks.

  • Brian
8

Okay. Will try and do so.
thank you so very much

9

Hi @upwardD just following up if did the response provided above help you solve your query?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

10

Hi @upwardD, we’ve noticed that no response has been received from you since Sep 15. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

11

Hi @upwardD, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.