DAX: Date filters for benchmark value

[readyraddy]

My data ranges from January 2020 to October 2020.

I am trying to display 2 different values based on my date slicer.

1st Visual = Calculated value based on date selected

2nd Visual (benchmark) = Calculated value based on the year of the date selected (eg. even if the date selected is from March to May, the value displayed should be from January to October)

Any way I can achieve this?

Thanks

[v-yangliu-msft · Accepted solution]

Hi  @readyraddy ,

According to your description, I created some data to show:

Here are the steps you can follow：

1. Create measure.

1st Visual = SUM('Table'[amount])

2nd Visual =
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),MONTH([Date])>=1&&MONTH([Date])<=10&&YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[readyraddy]

Noticed that using VAR in the measures will cause the value to filter despite the filtering statements.

[readyraddy]

I'm using this measure to get my YTD date.

How do I make use of this measure to obtain the Sales for that Year?

YTD_YEAR = YEAR(CALCULATE(MIN('Date'[Date]),ALLSELECTED('Date'[Date])))

[amitchandak]

@readyraddy , 12/31 end date year means 1/1 is the start of the year. If datesytd does not work try like

Measure =
var _max = maxx(allselected('Date') , 'Date'[Date])
var _min = minx(year(_max),1,1)
return
calculate(countrows('Table'), Filter(all('Date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

But make sure you are using date table and marked it as a date table.

[readyraddy]

I would like to try that measure by I'm getting an error here that says "Parameter is not the correct type."

var _min = minx(year(_max),1,1)

[amitchandak]

@readyraddy , With Date table you can use datesytd if date is selected from Date table

example
Within Range -SUM(Sales[Sales Amount]

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[readyraddy]

Thanks for the reply. I would like to understand the DATESYTD portion.

What is the purpose of indicating "12/31"?

Also, I have tried the formula. It doesn't work and the "YTD Sales" still was still filtered.