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,

 

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

    • SNV's avatar
      SNV
      Advocate I
      8 years ago

      Edited my first post, it seems to work.

       

      Kind regards

      • 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