cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
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
Helper I

Re: FIRSTDATE and LASTDATE

Thanks for your answer, but it doesn't work for me.

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
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
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

Expected AVG.PNG

Highlighted
Community Support
Community Support

Re: FIRSTDATE and LASTDATE

Hi @SNV,

 

It seems like I can't access to the shared file(no permission to access your sharepoint), can you please upload it to onedrive and share the link?

 

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
Helper I

Re: FIRSTDATE and LASTDATE

Edited my first post, it seems to work.

 

Kind regards

Highlighted
Community Support
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] )

2.gif

 

 

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
Helper I

Re: FIRSTDATE and LASTDATE

Hello Xiaoxin Sheng,

 

Thanks for your fast response.

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:

AVG1.PNG

 

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:

AVG2.PNG

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
Community Support

Re: FIRSTDATE and LASTDATE

HI @SNV,

 

I can't got your logic.

 

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.

7.PNG

 

 

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

8.PNG

 

Regards,

Xiaoxin Sheng

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors