locked
MAX and MIN date in Excel power pivot FILTER. RRS feed

  • Question

  • Hello Everyone,

    This question might be a basic one but i could not find the answer for this. I want to display the Min date and Max date of a date range in excel power pivot filter in  separate cells.Could anyone help me achieve this using excel formula.

    ****Date Hierarchi is always on Filter and not on rows.

    Eg:

    Pivot filter date range : 10-2-2015 to 25-2-2015  then two separate cell should show the min date as 10-2-2015 and max date as 25-2-2015.

    Kind Regards

    Mah

    Wednesday, June 7, 2017 12:00 PM

Answers

  • Hello Ed price,

    I am a bit late in answering your question.

    Actually i found an alternate solution for this problem. I dragged my date column into 'measure group' and assigned 'Max' and 'min' aggregation type. Whatever date i select in my power pivot it is showing the first and last date, its obvious. But it would look more beautiful(appropriate) if those dates are displayed as separate row somewhere in power pivot report (i know it can be possible by VBA but that is not my tea), instead of entire column. Unfortunately i have to fix this in excel pivot.

    now my soultion looks like this

    product     max date         min date

       a            2016-02-03    2016-01-03

       b            2016-02-03     2016-01-03

        --                ----                 ---

        ---               ---                  ----

    Instead of displaying them as columns, i would like to display max and min date from filter as a row like this (2016-01-03 to 2016-02-03)

    Kind Regards

    Mah

    Tuesday, October 17, 2017 12:47 PM

All replies

  • Hello,

    To return min and max dates in DAX we can use statistical functions : MIN()  and MAX () and time intelligence functions: LASTDATE() and FIRSTDATE() . Very simple.

    time intelligence functions:
    MAX Date with LASTDATE():=LASTDATE(DimDate[Date]) MIN Date with FIRSTDATE():=FIRSTDATE(DimDate[Date])

    statistical functions: MAX Date with MAX():=MAX(DimDate[Date]) MIN Date with MIN():=MIN(DimDate[Date])


    N-






    Wednesday, June 7, 2017 2:25 PM
  • Hello Nick,

    My excel pivot is running against OLAP cube.

    Kind Regards

    Mah

    Wednesday, June 7, 2017 2:41 PM
  • When you say you are running Power Pivot against OLAP, could you please confirm the following :

    Did you use MDX or the wizard to shape your tables and import the data into Power Pivot ?

    N -


    Wednesday, June 7, 2017 3:34 PM
  • I am using Wizard to import the data and i tried to write MDX for finding the max date and min date from filter, but it is working for single date,month or year and not for a date range.

    Head(Descendants(Time.Datetime.currentmember, Time.Datetime.Day),1).item(0).member_value - max date

    Tail( same as above) -  min date.

    Kind Regards

    Mah

    Thursday, June 8, 2017 6:38 AM
  • Mah, any progress on this?

    Also, you might want to try this question on the PowerBI.com forums.

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, October 9, 2017 8:51 PM
  • Hello Ed price,

    I am a bit late in answering your question.

    Actually i found an alternate solution for this problem. I dragged my date column into 'measure group' and assigned 'Max' and 'min' aggregation type. Whatever date i select in my power pivot it is showing the first and last date, its obvious. But it would look more beautiful(appropriate) if those dates are displayed as separate row somewhere in power pivot report (i know it can be possible by VBA but that is not my tea), instead of entire column. Unfortunately i have to fix this in excel pivot.

    now my soultion looks like this

    product     max date         min date

       a            2016-02-03    2016-01-03

       b            2016-02-03     2016-01-03

        --                ----                 ---

        ---               ---                  ----

    Instead of displaying them as columns, i would like to display max and min date from filter as a row like this (2016-01-03 to 2016-02-03)

    Kind Regards

    Mah

    Tuesday, October 17, 2017 12:47 PM