Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Difference between VALUES(Date[Month]) and VALUES(Date[Day])

For context, please see previous question   I have DAX that calculates the YTD average on an ongoing basis of a measure (see below and previous question for more detail):     where the blue...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    I actually managed to solve my issue! By using the following DAX code:

     IF(
	ISFILTERED('Date Table'[Datekey]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	TOTALYTD([Monthly Reliability], 'Date Table'[Datekey].[Date])
)
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Two initial thoughts. 1 - how is the value for selected_year obtained.  I don't see it in the expression.  2 - what is the calculation made for each day or month (depending on the calculation)?  If it is not a simple aggregation, you should not expect same result for daily average vs. overall avg for January.

Pat

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Pat,

 

1 - selected_year is simply a slicer that can be selected. It contains 'Date table'[Year] (edited original post)

 

var selected_year = SELECTEDVALUE('Date Table'[Year])

 

2 - This sounds interesting, could you expand on it a little please? The calculation "Monthly Availability" that is used to derive an average is:

 

 SUM('AIF Deferments'[Unscheduled_kboe])/(sum('AIF Stream Values Pivot'[Export Total kBOE])+sum('AIF Deferments'[Scheduled_kboe])+sum('AIF Deferments'[Unscheduled_kboe]))*100

 

     Essentially, just a ratio of one column to others in the same row.

 

Please let me know if that hasn't addressed your questions.