cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leumvs Frequent Visitor
Frequent Visitor

Calculate measure for current date

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.

 

 

question.png

 

 

 

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Calculate measure for current date

Hi @leumvs ,

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
leumvs Frequent Visitor
Frequent Visitor

Re: Calculate measure for current date

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