Forum Discussion

SNV's avatar
SNV
Advocate I
8 years ago

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 narro...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

SNV's avatar
SNV
Advocate I
8 years ago

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:

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

     

     

    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

  • SNV's avatar
    SNV
    Advocate I
    8 years ago

    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.