Power BI Exchange

Expand all | Collapse all

Calculating First Sale Date Dynamically and Statically

Jump to Best Answer
  • 1.  Calculating First Sale Date Dynamically and Statically

    Posted 9 days ago
    ​Hello,

    I am trying to calculate first sale date dynamic to a date slicer and the following works:

    First Sales Date =
    VAR
        FirstSale= FIRSTDATE(Sales[Purchase Date])
    Return
        FirstSale

    This creates a first sale date in the selected date slicer. Example: if the year selected is 2016 the earliest first sale date you will find is 1/1/2016.

    Now I need to create the first sale date regardless of the date slicer. I know I need a calculate function and possibly a filter function, but I can't quite get it right. Example: if the year selected in the slicer is 2016 it will still calculate the first sale of say 1/1/2015.

    I have tried:

    First Sales Date =
    VAR
        FirstSale= FIRSTDATE(Sales[Purchase Date])
    Return
        CALCULATE(FirstSale, ALL(Sales))

    AND

    First Sales Date =
    VAR
        FirstSale= FIRSTDATE(Sales[Purchase Date])
    Return
        CALCULATE(FirstSale, ALL(Dates))

    Any ideas?

    ------------------------------
    Malori Meyer
    Analyst
    9798249358
    ------------------------------


  • 2.  RE: Calculating First Sale Date Dynamically and Statically

    Posted 9 days ago
    Edited by Malori Meyer 9 days ago


    ------------------------------
    Malori Meyer
    Analyst
    9798249358
    ------------------------------



  • 3.  RE: Calculating First Sale Date Dynamically and Statically

    Top Contributor
    Posted 9 days ago
    Hi,

    Hopefully this will solve your issue;

    First Sales Date = FIRSTDATE(Sales[Purchase Date])

    First Sales Date1 = CALCULATE([First Sales Date], ALL(Dates))

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------



  • 4.  RE: Calculating First Sale Date Dynamically and Statically
    Best Answer

    Bronze Contributor
    Posted 6 days ago
      |   view attached
    Hi @Malori Meyer

    The issue with your DAX is that, the Variable "FirstSale" would have been already calculated with the context filters in place (that is the Date in slicer). The CALCULATE after the RETURN will not again open up the context for the variable already calculated. You should do the opening in the variable itself.
    Please see the following codes:

    1.       The way you have calculated

    First Sale Date 1 = 
    VAR firstsale =
        FIRSTDATE ( 'Sales Table'[Sale Date] )
    RETURN
        CALCULATE ( firstsale, ALL ( 'Sales Table' ) )

    2. DAX with the opening of the context filter in the variable itself

    First Sale Date 2 = 
    VAR firstsale =
        CALCULATE(FIRSTDATE ( 'Sales Table'[Sale Date] ),ALL('Sales Table'))
    RETURN
        firstsale
    thumbnail image

     

    Hope this helps. PFA the pbix file also for reference.



     



    ------------------------------
    Gopa Kumar S

    ------------------------------

    Attachment(s)

    pbix
    First Sale Date.pbix   46K 1 version