cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
spenot09
Helper V
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): 

Month DAX.PNG

 

 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: 

DAY DAX.JPG

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
spenot09
Helper V
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])
)

View solution in original post

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

View solution in original post

mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.