none
Calculate max date in a month with offset RRS feed

  • Question

  • Hi team,

    I just got a simple model like this:

    In this simple model I created a measure to calculate the max date with positive 2 days offset:


    And I get the following result:

    In the part highlighted with yellow color, the result looks pretty funny.

    For the line of 29/Nov/2017, due to the filter of month (201711), even the calculate removed the filter on the date, the filter on the month should still work and the result returned by this calculation should be 30 Nov 2017 (the max date value in 201711 should be 30 Nov 2017). The same logic should also apply to 30 Nov 2017 line.

    However, it seems this calculation does not apply that rule.

    My question is, with removing the filter on date[date], is the filter on date[Month] still valid in this case? If that is true, why the calculation does not return the result as I expected?

    Kind regards

    Tom Sun

    Friday, February 9, 2018 12:11 PM

Answers

  • Hi Matt,

    Thanks for the reply.

    For the removal of the primary key, it does not remove the filter place on the entire table. The evidence can be seen as follow:

    As you can see from this measure, with removing the filter placed on the primary key, the month filter still work and return the number of dates from month 201711 only. So this is not the cause for this issue.

    I also did some further test with this calculated table:

    In this calculated table, I place the filter on month in the outer calculatetable function, and the inner calculatetable function changes the filter context placed on the date column, which is the primary key of this table, and the result returns the month from 201712, which is not the result as I expected. I assume in certain circumstances, this is going to remove the filter placed by the month, which I still not quite understand.

    In addition, if I placed the filter context from month in calculate function and rebuild that measure, the result match with my expectation.

    So now the real question is when and where the removal of filter placed on the primary key is going to override the filters from other columns in that table?

    Kind regards,

    Tom Sun



    • Edited by Tom J Sun Saturday, February 10, 2018 10:21 AM
    • Marked as answer by Tom J Sun Monday, February 12, 2018 5:01 AM
    Saturday, February 10, 2018 10:19 AM

All replies

  • My understanding is if you remove a filter on the primary key column, you are effectively removing the filter from the entire table. Try adding VALUES(cal[month]) to add back the month filter context. Finally, when you add 2 to a date, it adds 2 to the date. I don’t think you are going to get it to return the max of the month with this formula. You could try a good old max(option 1, option 2) to return the max of your formula or the last date of he month in the current filter context.

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

    Saturday, February 10, 2018 6:33 AM
    Answerer
  • Hi Matt,

    Thanks for the reply.

    For the removal of the primary key, it does not remove the filter place on the entire table. The evidence can be seen as follow:

    As you can see from this measure, with removing the filter placed on the primary key, the month filter still work and return the number of dates from month 201711 only. So this is not the cause for this issue.

    I also did some further test with this calculated table:

    In this calculated table, I place the filter on month in the outer calculatetable function, and the inner calculatetable function changes the filter context placed on the date column, which is the primary key of this table, and the result returns the month from 201712, which is not the result as I expected. I assume in certain circumstances, this is going to remove the filter placed by the month, which I still not quite understand.

    In addition, if I placed the filter context from month in calculate function and rebuild that measure, the result match with my expectation.

    So now the real question is when and where the removal of filter placed on the primary key is going to override the filters from other columns in that table?

    Kind regards,

    Tom Sun



    • Edited by Tom J Sun Saturday, February 10, 2018 10:21 AM
    • Marked as answer by Tom J Sun Monday, February 12, 2018 5:01 AM
    Saturday, February 10, 2018 10:19 AM