locked
First Value for a Date Outside of a Time Slicer RRS feed

  • Question

  • Hi,

    I've been using this measure to get the cost of the most recent cost of a product within the context of timeline slicer.

    Curr. Total USD:=CALCULATE(AVERAGE(Table1[Total Cost USD]),LASTNONBLANK(DATESBETWEEN(Table1[Costing Date (Key)],BLANK(),LASTDATE(Table1[Costing Date (Key)])),CALCULATE(COUNT(Table1[Total Cost USD]))))

    Is there a way to adapt this measure to show the value for the first previous date outside of the time filter?

    Thanks,

    Thursday, January 5, 2017 8:42 PM

Answers

  • Wow, bad hair day my end.  I had 2 copies of DAXFORMATTER open and I cut and paste the wrong formula.  I did have it working.  This is the correct one.

    Previous =
    AVERAGEX (
        SUMMARIZE ( data, Data[Plnt], Data[Material] ),
        CALCULATE (
            CALCULATE (
                CALCULATE ( MAX ( Data[Total Cost USD] ), LASTDATE ( data[Date] ) ),
                FILTER (
                    ALLEXCEPT ( data, Data[Plnt], Data[Material] ),
                    data[date] < ( MIN ( data[date] ) )
                )
            )
        )
    )
    You will note my previous description of how this formula works matches this formula, not the earlier one I posted



    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Saturday, January 7, 2017 9:29 PM
    Answerer
  • I haven't looked at the calc column option, but yes it could be a good option.  A friend (Owen Auger) proposed this formula as a measure.  Maybe give that a go and see what the performance is like

    =
    AVERAGEX (
        SUMMARIZE ( data, Data[Plnt], Data[Material] ),
        CALCULATE (
            MAX ( data[Total Cost USD] ),
            CALCULATETABLE (
                LASTDATE ( data[Date] ),
                DATESBETWEEN ( data[Date], BLANK (), MIN ( data[Date] ) - 1 )
            )
        )
    )
    


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    • Marked as answer by Wichtek Thursday, January 19, 2017 12:22 AM
    Sunday, January 15, 2017 11:38 PM
    Answerer

All replies

  • It's hard to give specific advice without seeing sample data. Your measure looks more complex than I think it needs to be, but once again I can't be sure without seeing it. Generally your measures should try to filter, then evaluate. Your slicer provides initial filter context in the visual, so for your current measure all you should have to do is find the last date (hence I don't see a need for datesbetween). For your question, when you say outside of the time filter, I assume you mean "prior to". To do this, you simply need to first find the first date in the current filter context, and then apply a filter to be prior to that first date. =calculate ([your current measure],table1[costing date] < firstdate(table1[costing date])) I can't be 100% sure of he formula without seeing the data model, but that is the general approach.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au



    Thursday, January 5, 2017 11:36 PM
    Answerer
  • Thanks for the response. I agree that this measure is overkill but its a copied code of what I am trying to get the first date outside of the filter. Here is a subset of the data that I am using.

    Costing Date (Key) Plnt Material Total Cost USD
    1/1/2016 0:00 2810 5001021 6.25
    4/1/2016 0:00 2810 5001021 6.25
    7/1/2016 0:00 2810 5001021 6.25
    8/1/2016 0:00 2810 5001021 6.33
    10/1/2016 0:00 2810 5001021 6.33
    11/1/2016 0:00 2810 5001021 6.33
    1/1/2016 0:00 1890 5001021 7.31
    4/1/2016 0:00 1890 5001021 7.31
    7/1/2016 0:00 1890 5001021 7.31
    10/1/2016 0:00 1890 5001021 7.31

    If I have timeline filter for the range of 8/1/2016 to 10/31/2016. I would like the measure to provide the current cost for the last date in that range (10/1/2016 - 6.33). The previous cost measure would should be the cost at the first date before the filter range (7/1/2016 - 6.25).

    The approach you proposed doesn't seem to work in a measure as it gives an error saying I can't use firstdate as a true or false in a filter. 

    Friday, January 6, 2017 2:52 PM
  • The current measure I am using to get that date is:

    Prev. Total USD:=CALCULATE(AVERAGE(Table1[Total Cost USD]),LASTNONBLANK(DATESBETWEEN(Table1[Costing Date (Key)],BLANK(),MIN(Table1[CalculatedColumn2])),CALCULATE(COUNT(Table1[Total Cost USD]))))

    CalculatedColumn2 gives the previous costing date in a column. If it is the first date it creates a blank.

    This measure provides the correct value if the pivot table table is fully expanded for plant and material. When I minimize the material it doesn't provide the correct previous average cost for the materials in that plant.

    Friday, January 6, 2017 3:00 PM
  • This is what I would use for your first measure.  It iterates over a matrix of all the Plants and Materials in the current filter context and should give the correct average at all levels.  Note it is a measure, not a column

    Current =AVERAGEX (
        SUMMARIZE(data,Data[Plnt],Data[Material]),
            CALCULATE ( MAX ( Data[Total Cost USD] ), LASTDATE ( data[Date] ) )
        )

    I assume you will have more materials later and the measure should handle that correctly. Although it isn't tested (as the test data only has 1 material) I believe it should still work .  Note it is good practice to have a set of test data that is reflective of your end state scenario, so in this case a second material.

    Edit:

    Here is the second formula.

    Previous =
    AVERAGEX (
        SUMMARIZE ( data, Data[Plnt], Data[Material] ),
        CALCULATE (
            CALCULATE (
                MAX ( Data[Total Cost USD] ),
                FILTER (
                    ALLEXCEPT ( data, Data[Plnt], Data[Material] ),
                    data[date] < ( MIN ( data[date] ) )
                )
            )
        )
    )



    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au






    Friday, January 6, 2017 10:49 PM
    Answerer
  • Thanks. Works like a charm except for Nov and Dec. When I filter on these months the previous cost always seems to be pulling the Sept cost value. Using the data below if the filter was selected for November it would show 6.58724 as the current costs and the previous cost as 6.58823. For every month from January to September there is no issue.

    Cost Date Plnt Material Total Cost USD
    1/1/2016 0:00 7942 5011022 5.39153333
    2/1/2016 0:00 7942 5011022 5.39153333
    3/1/2016 0:00 7942 5011022 6.46626667
    4/1/2016 0:00 7942 5011022 6.46626667
    5/1/2016 0:00 7942 5011022 6.54898519
    6/1/2016 0:00 7942 5011022 6.58691852
    7/1/2016 0:00 7942 5011022 6.58691852
    8/1/2016 0:00 7942 5011022 6.58691852
    9/1/2016 0:00 7942 5011022 6.58822963
    10/1/2016 0:00 7942 5011022 6.58724444
    11/1/2016 0:00 7942 5011022 6.58724444
    12/1/2016 0:00 7942 5011022 6.60365926

    Would you be able to explain what the double calculates are doing?

    My guess would be to make sure that equation in the second filter is carried out on each row.

    Thanks again!

    Saturday, January 7, 2017 1:43 AM
  • In the scenario you mentioned, (filter Nov/Dec), the last cost in the period prior to Nov was actually 6.58724444 (same as Nov in this case).  As you mentioned earlier "The previous cost measure would should be the cost at the first date before the filter range".   This is exactly what is being returned = 6.58724444

    If you want to find the price prior to the selected period and also different to the first cost in the current period, then that is more complex.

    Double Calculate

    A calculate has the syntax =CALCULATE(Expression,filters)

    The filter portion of the CALCULATE is executed in the current filter context - whatever that is.

    Using the line numbers provided below


    AVERAGEX (line 2) is an iterator and operates in a Row Context.  (important note: A Row Context is different to a Filter Context).  AVERAGEX iterates over a virtual table created by SUMMARIZE (line 3).  Because this (line 3) is a virtual table, it behaves just like a lookup table in the data model.  Because SUMMARIZE is effectively a lookup table, there is effectively a relationship between this new SUMMARIZE table and the data table.  

    AVERAGEX iterates in a row context, however a row context is not a filter context.  So without the additional CALCULATE (line 4), the "filter" portion of the inner CALCULATE (Lines 7 - 10) will be evaluated in the initial filter context coming from the visual (eg pivot table).  Even though there is an iteration over the SUMMARIZE table, this iteration (row context) is not converted to a filter context, hence the data table is not filtered at all by this iteration.

    Calculate (Line 4) converts the row context from AVERAGEX into an equivalent filter context, hence the data table is then filtered for just the Plant/Material combination being currently iterated by AVERAGEX. 

    I know it is complex.  You can read my article here and that may help. http://exceleratorbi.com.au/many-many-relationships-dax-explained/


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au



    Saturday, January 7, 2017 2:58 AM
    Answerer
  • Thanks for that explanation. That answered the question I was having a hard time getting a hold of on how to make sure each row calculation is filtered.

    Please look below at the issue I'm having with the filters. Using the data above, when November is selected the current cost is correct. However, the previous cost is for September instead of October. This same issue happens for December. 



    Saturday, January 7, 2017 3:36 AM
  • ah yes.  My bad again - sorry :-(

    Try this.

    =
    AVERAGEX (
        SUMMARIZE ( data, Data[Plnt], Data[Material] ),
        CALCULATE (
            CALCULATE (
                CALCULATE (
                    MAX ( Data[Total Cost USD] ),
                    FILTER (
                        ALLEXCEPT ( data, Data[Plnt], Data[Material] ),
                        data[date] < ( MIN ( data[date] ) )
                    )
                )
            )
        )
    )
    I think the triple calculate is needed.  You could try without the outermost calculate if you like, but I think it is required.

    The outermost calculate creates context transition.  The next one filters out the current selection in the slicer.  The innermost one takes the value from the last remaning date prior to the filter context

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au




    Saturday, January 7, 2017 4:36 AM
    Answerer
  • Btw, there are probably more efficient ways to do this, but if your data set is small it really doesn't matter

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Saturday, January 7, 2017 4:55 AM
    Answerer
  • That doesn't seem to do it.

    The reason why the measure doesn't update the previous cost value in November with the cost value for October is because it is looking for the max cost value at anytime before October and not just for October. September has a higher cost then October.

    I've been trying modify the measure using this logic:

    1. Create a summary table for all dates less than the current filter data[Date]<(MIN(data[date]),Plant, and Material

    2. Get the max date from that summarized table.

    3. Apply that max date as a filter in the measure.

    Let's just say I haven't been to successful. Any thoughts would be appreciated.

    ve tried to validate where the error happens by breaking out the
    Saturday, January 7, 2017 3:11 PM
  • I can get the last date for the date previous to the filter using this expression:

    :=CALCULATE(MAX(Table1[Costing Date (Key)]),

    FILTER(ALLEXCEPT(Table1,Table1[Plnt],Table1[Material]),

    Table1[Costing Date (Key)]<(MIN(Table1[Costing Date (Key)]))))

    I am unsure how to filter my table to get the cost value for a plant and material at that particular date.


    • Edited by Wichtek Saturday, January 7, 2017 3:43 PM
    Saturday, January 7, 2017 3:43 PM
  • Wow, bad hair day my end.  I had 2 copies of DAXFORMATTER open and I cut and paste the wrong formula.  I did have it working.  This is the correct one.

    Previous =
    AVERAGEX (
        SUMMARIZE ( data, Data[Plnt], Data[Material] ),
        CALCULATE (
            CALCULATE (
                CALCULATE ( MAX ( Data[Total Cost USD] ), LASTDATE ( data[Date] ) ),
                FILTER (
                    ALLEXCEPT ( data, Data[Plnt], Data[Material] ),
                    data[date] < ( MIN ( data[date] ) )
                )
            )
        )
    )
    You will note my previous description of how this formula works matches this formula, not the earlier one I posted



    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Saturday, January 7, 2017 9:29 PM
    Answerer
  • Works great.

    Funny story. I added a lastdate filter while I was trouble shooting, but added it in the wrong calculate. Close but no cigar.

    You weren't kidding about the computational hit. I'm going to play around with it and see if I can alter it to speed it up. Your explanations have helped in understanding row and context filtering in DAX.

    Thanks for your help!


    Sunday, January 8, 2017 12:19 AM
  • Sorry for the false starts. I will take another look when I am bored. I am thinking to use dax queries to produce a table, and then operate over that table. I will post back if I get anything.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, January 8, 2017 1:39 AM
    Answerer
  • Hi Wichteck,

    If you have resolved your question, please mark the corresponding replay as answer for help more people clearly, thanks for your understanding.

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 1:41 AM
  • Is it possible to have a column to perform a similar calculation to determine the previous value? I think this would improve the speed of the pivot table for a one time hit during the data model.

    Does this make sense?

    I have gotten halfway. I am able to determine all previous max days but I am unable to nest this as a filter to determine the value for the previous costing date.

    Previous Month =CALCULATE(MAX([Costing Date (Key)]),FILTER(Table1,EARLIER([CalculatedColumn1])=([CalculatedColumn1])&&([Costing Date (Key)])<EARLIER([Costing Date (Key)])))

    CalculatedColumn1=[Plnt]&[Material]

    Any thoughts on this rationale?

    Monday, January 9, 2017 2:12 PM
  • I haven't looked at the calc column option, but yes it could be a good option.  A friend (Owen Auger) proposed this formula as a measure.  Maybe give that a go and see what the performance is like

    =
    AVERAGEX (
        SUMMARIZE ( data, Data[Plnt], Data[Material] ),
        CALCULATE (
            MAX ( data[Total Cost USD] ),
            CALCULATETABLE (
                LASTDATE ( data[Date] ),
                DATESBETWEEN ( data[Date], BLANK (), MIN ( data[Date] ) - 1 )
            )
        )
    )
    


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    • Marked as answer by Wichtek Thursday, January 19, 2017 12:22 AM
    Sunday, January 15, 2017 11:38 PM
    Answerer