Calculate measure for current date

[Anonymous]

Let me first explain the data structure. There is one table called "Query" with the following columns:
"Ticker" (type=text) contains ticker names
"Bucket" (type=text) contains time buckets 00-01, 01-03, etc.
"ID" (type=text) contains a unique text string
"Score" (type=decimal number) contains decimal numbers
"Date" (type=date) contains dates.

A Ticker has several IDs. Every ID lies in a Bucket. Every ID has a Score and several dates.

On the page there is a Date Slicer, where I can move the current date.
In the following matrix visual I have represented the measure "Current Average Score". The matrix has two levels (rows) which are expanded in the picture. The first level is Ticker and the second level is Bucket.

The measure "Current Average Score" should calculate the average score in the right context on the current date.

However, in some contexts, for example in Bucket 01-03 for Ticker EIB or in Bucket 07-10 for Ticker IADB, the last data entries have a date earlier than the current date.

In order to solve the problem I defined two measures:
Last Date = LASTDATE(Query[Date])
Current Date = LASTDATE(ALLSELECTED(Query[Date]))

Then I defined the measure "Current Average Score" like this:

Current Average Score = CALCULATE(AVERAGE(Query[Score]); FILTER(Query; Query[Date] = Query[Current Date]))

Unfortunately, the measure "Current Average Score" is calculated for the Last Date and not the Current Date. I would like the measure to be blank if there is no data available for the Current Date.

How can I achieve this result?

I am aware of the fact, that the issue would be resolved if I didn't select a date range in the Date Slicer but instead only a single date but I need the date range for other measures on the same page.

[v-danhe-msft]

Hi @Anonymous ,

Could you please post some simple sample data and your desired result to have a test if possible? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Daniel He

[Anonymous]

Thanks @v-danhe-msft  for the reply. Here is some sample data in csv format:

Ticker;Bucket;ID;Score;Date
EIB;00-01;EIB1;30;20.02.2019
EIB;01-03;EIB1;27;31.01.2019
IADB;05-07;IADB1;53;20.02.2019
IADB;07-10;IADB1;85;31.01.2019