cancel
Showing results for
Did you mean:
Highlighted
Helper I

## FIRSTDATE and LASTDATE

Hello community,

Building on a previous solution for an answer, I'm using FIRSTDATE and LASTDATE to get the first and most recent occuring date.

Problem is, whenever I use a date slicer to narrow the range, some dates don't change. So if I set the slicer to 1-1-2017 through 31-12-2017, a date for 1-1-2015 will still be returned.

The code I use for finding the minimum date is:

`Minimum Date = CALCULATE(FIRSTDATE('Table'[Date]);ALLEXCEPT('Table';'Table'[Group];'Table'[ID]))`

Then I use that measure for calculating an average with this code:

`AVG MIN DATE = VAR Tbl2 =   SUMMARIZE(    FILTER('Table';'Table'[Date]='ZRM TEST QUERY'[Minimum Date]);'Table'[Group];'Table'[ID];"AVG_PER_ID_ON_MIN_DATE";AVERAGE('Table'[SCORE]))RETURN AVERAGEX(Tbl2;[AVG_PER_ID_ON_MIN_DATE])`

But for some ID's it returns the incorrect average because of the mistake with the minimumdate.

Any help would be much appreciated

10 REPLIES 10
Highlighted
Community Support

## Re: FIRSTDATE and LASTDATE

Hi @SNV,

You can try to use below formula if it suitbale for your requirement:

```AVG MIN DATE =
VAR lastdate =
MINX ( ALLEXCEPT ( 'Table'; 'Table'[Group]; 'Table'[ID] ); [Date] )
VAR Tbl2 =
SUMMARIZE (
FILTER ( ALLSELECTED ( 'Table' ); 'Table'[Date] = lastdate );
'Table'[Group];
'Table'[ID];
"AVG_PER_ID_ON_MIN_DATE"; AVERAGE ( 'Table'[SCORE] )
)
RETURN
AVERAGEX ( Tbl2; [AVG_PER_ID_ON_MIN_DATE] )
```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper I

## Re: FIRSTDATE and LASTDATE

In the visuals it doesn't show any data.

With my own measures, when I manually filter the rows for only 01-01-2017 through 31-12-2017 in the query editor, close and apply, my measure works because there aren't any earlier dates.

For now I will keep using that way of "filtering", hopefully a solution for using the date slicer will be found

Highlighted
Community Support

## Re: FIRSTDATE and LASTDATE

Hi @SNV,

If you can please share the sample pbix file to test, I will test on it.(I'm not so sure if I can find a solution)

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper I

## Re: FIRSTDATE and LASTDATE

Hello Xiaoxin Sheng,

Attached is the sample .pbix and a screenshot with the expected outcome.

In the screenshot you can see the averages per group for 2017. These values are what I am looking for.

If I am only using the date slicer with the measures in the visual I am not getting these values, as you can see in the sample.

When I filter the rows in the query editor to only include dates in 2017 I get the correct averages

Any help with getting the correct averages by only using the date slicer would be much appreciated.

Kind regards

EDIT: I couldn't share the first link (permissions etc.) Hopefully this link will work. OneDrive Sample

Highlighted
Community Support

## Re: FIRSTDATE and LASTDATE

Hi @SNV,

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper I

## Re: FIRSTDATE and LASTDATE

Edited my first post, it seems to work.

Kind regards

Highlighted
Community Support

## Re: FIRSTDATE and LASTDATE

Hi @SNV,

After I modify your formula, chart graph seems works again, you can take a look at below formula.

```AVG MAX DATE =
VAR Tbl3 =
SUMMARIZE (
FILTER ( 'SampleData', [ZRM.Date] = [Maximum Date] ),
[Group],
[ZRM.ID],
"AVG_PER_ID_ON_MAX_DATE", AVERAGE ([SCORE] )
)
RETURN
AVERAGEX ( Tbl3, [AVG_PER_ID_ON_MAX_DATE] )

AVG MIN DATE =
VAR Tbl2 =
SUMMARIZE (
FILTER ( 'SampleData', [ZRM.Date] = [Minimum Date] ),
[Group],
[ZRM.ID],
"AVG_PER_ID_ON_MIN_DATE", AVERAGE ( [SCORE] )
)
RETURN
AVERAGEX ( Tbl2, [AVG_PER_ID_ON_MIN_DATE] )
```

Notice:
1. It seems original table column name not works on filtered table, you can remove prefix table name to direct use column name, power bi will recognize to the filtered temp table.

2. I attached the modified sample file below.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Helper I

## Re: FIRSTDATE and LASTDATE

Hello Xiaoxin Sheng,

I took a look at your file, but I'm still not getting the correct averages.

Maybe I didn't explain it well enough.

If  I use the date slicer to filter my averages for only 2017, I get the output below:

The problem is that a date that is earlier than the selected range is used within the measures.

When I filter the rows in the query editor to only dates in 2017 I get the following output:

For the same ID the date now is within 2017, which is what I want.

Does this explain my problem more clearly?

Kind regards

Highlighted
Community Support

## Re: FIRSTDATE and LASTDATE

HI @SNV,

In your visual, you use the 'group' as the axis, so this graph shows summarized result by group column. When you filter on matrix by specific ID, then graph will change.

BTW, if you want to use show the correspond result, you need to modify your chart and use 'ID' as the axis.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021