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,

 

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

    • SNV's avatar
      SNV
      Advocate I
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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