Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Employee
Employee

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


Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.