CODEWITHSUNDEEP
powerbidaxcalculated-field
Mehek Salim
Mehek Salim

Reputation: 3

Getting blanks when trying to calculate the current year avg. no of bins, how can i resolve this? Issue seems to rise when i put in the date argument

I want to create a measure for current year average no. of bins. I tried the below possibilities and all are giving blanks. i tested to see if it's an issue with my date column but when i filter the data based on the year it works but somehow i'm not able to capture it into a measure. What is causing this error? My date format is correct.

Wanted to show current year compared to previous year but unable to get current year value. Here are the DAX expressions used:

Approach 1:

Bins CY Avg = 
CALCULATE(
AVERAGE('All-Pivoted'[Value]), 
'All-Pivoted'[Component] = "Bins/ Containers",
YEAR('All-Pivoted'[Year]) = YEAR(TODAY())

Approach 2:

Bins CY Avg2 = 

VAR CurrentYear = MAX('All-Pivoted'[Year].[Year])
RETURN 
CALCULATE(
AVERAGE('All-Pivoted'[Value]), 
'All-Pivoted'[Component] = "Bins/ Containers",
FILTER(
    'All-Pivoted',
    YEAR('All-Pivoted'[Date]) = CurrentYear
)
)

Upvotes: -1

Views: 27

Answers (1)

APB Reports
APB Reports

Reputation: 2451

Try this:

Bins CY Avg2 = 
VAR CurrentYear = YEAR(TODAY())
RETURN
CALCULATE(
    AVERAGE('All-Pivoted'[Value]),
    'All-Pivoted'[Component] = "Bins/ Containers",
    YEAR('All-Pivoted'[Date]) = CurrentYear
)

The CALCULATE function in DAX inherently applies filters to the data. So when you pass conditions like 'All-Pivoted'[Component] = "Bins/ Containers" and YEAR('All-Pivoted'[Date]) = CurrentYear inside the CALCULATE function, it filters the data accordingly.

You don't need to explicitly use a FILTER function unless you need more complex or nested filtering logic.

Upvotes: 0

Related Questions