Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Return values without the last date in data

Hi guys, need help here

 

My last date of data is not today. 

I have a slicer that is after x date to the latest production date. However, In my values I do not wish to display (it is showing a large dip at the end because the data is incomplete mid-day)

 

I am trying to find the difference between the dates after, and the selected date, while excluding the most recent date.

This is my formula without excluding the most recent date - which is not today() but a date from the data source

DeltaProduction = SUM(table[production]) - CALCULATE(SUM(table[production]), FIRSTDATE(ALLSELECTED(Table[productionDate])))

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , You can try a new measure like

     

    new measure =
    var _max =maxx(allselected(date), Date[date])
    return
    calculate([measure], filter(Date, Date[date] <_max))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 


      Graph comes out bad 😕

       

      DeltaProduction = SUM(Genscape_HFOPM[Production]) - CALCULATE(sum(Genscape_HFOPM[Production]), FIRSTDATE(ALLSELECTED(Genscape_HFOPM[ProductionDate])))

      DeltaWOMostRecent =
      VAR Maximum = MAXX(ALLSELECTED(Genscape_HFOPM), Genscape_HFOPM[ProductionDate])
      Return
      CALCULATE([DeltaProduction], FILTER(Genscape_HFOPM, Genscape_HFOPM[ProductionDate]< Maximum))



       

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello @nicholaskong ,

    In my opinion, in order to avoid a big fall, you want to sum the production after the selected date and calculate the difference between the selected one and the sum, right?

    You can use the following formula:

    DeltaProduction =
VAR _total =
    CALCULATE (
        SUM ( 'Table'[Production] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ProductionDate] > MIN ( 'forSlicer'[ProductionDate] )
                && 'Table'[ProductionDate] < MAX ( 'forSlicer'[ProductionDate] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[ProductionDate] ) < MIN ( forSlicer[ProductionDate] ),
        SUM ( 'Table'[Production] ) - _total,
        0
    )

    10.14.1.PNG

    Here is the mypbix file.

    Have I answered your question? Please mark my answer as a solution. Thanks a lot.
    If not, load some insensitive data samples and expected output.

    Best regards
    Eyelyn Qin