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

 

 

Need Help

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

    • SNV's avatar
      SNV
      Advocate I
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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