Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SNV
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
v-shex-msft
Community Support
Community Support

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.

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

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.

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

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.

Edited my first post, it seems to work.

 

Kind regards

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.

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

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.

Hello Xiaoxin Sheng,

 

The way I want it to work is as follows:

- With the slicer a date range is selected. For example, 01-01-2017 through 31-12-2017.

- I now want the average per group from the scores with a FIRSTDATE within this date range and the average per group from the scores with a LASTDATE within this date range.

- If an ID has multiple scores on the same FIRSTDATE or LASTDATE that should first be averaged before the group average is calculated.

 

The problem is that while a date range is selected with the slicer, for some ID's a date is selected which falls out of this range. As seen in my previous post, a date is shown from 2015. This is not correct, and the average is miscalculated because of that.

 

Is this more clear?

 

Kind regards.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.