cancel
Showing results for
Did you mean:  Helper V

## 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 line is the monthly measure and the black line represents a running year-to-date average (e.g. in March the monthly availability values for January, February, March are summed and divided by 3). The DAX for this is:

``````YTD Availability=

var selected_year = SELECTEDVALUE('Date Table'[Year])
VAR maxdate = MAX('Date Table'[Datekey])
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Date Table'[Month] ),
[Monthly Availability]
),
'Date Table'[Year]=selected_year,
'Date Table'[Datekey] <= maxdate
)``````

where selected_year is a slicer selecting which year the user wants to view,

``````Monthly Availability =
UM('AIF Deferments'[Unscheduled_kboe])/(sum('AIF Stream Values Pivot'[Export Total kBOE])+sum('AIF Deferments'[Scheduled_kboe])+sum('AIF Deferments'[Unscheduled_kboe]))*100``````

However,the above does not show a the true year-to-date average as the number of days within a month are not equal (e.g. Jan has 31, Feb 28, etc.). To calculate a true YTD average I thought about simply switching out

``VALUES ( 'Date Table'[Month] ) --> VALUES ( 'Date Table'[Day] )``

This then causes the visual to look like this: As you can see, January (the first month) should be equal for both Monthly Availability and YTD Availability but the YTD value is incorrect. BUT the YTD average for December (77.5%) is what is expected when the number of days in each month are taken into account. After manual testing, it appears the discrepancy between the true YTD average and the one shown keeps decreasing until it becomes correct in December.

This is beyond puzzling for me and can't quite figure out what's going on. Any help would be much appreciated in getting a DAX calculation that works correctly, whilst taking into account the unequal days in each month. 🙂

@mahoneypatyou were kind enough to help me previously, any suggestions?

1 ACCEPTED SOLUTION  Helper V

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])
)``````
3 REPLIES 3  Helper V

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])
)``````  Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!  Helper V

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.  Announcements #### Welcome to the User Group Public Preview  