Showing results for 
Search instead for 
Did you mean: 
Angel Member

last date regardless context

Hi, everyone


Please, help!!!!!



The matrix is composed by:


        WeekCount --> Number of week regardless year (starts 01/01/2010)

        Rel.Week --> Relative week respect current one

        Snapshot date --> Snapshot date 


        Sales month --> Month where sales have been occurs


        Sales amount --> Amount for sales.

        LastDay --> Last day weeks selected


I want to consider only sales where sales date > Last snapshot selected. To do that I think must fix lastday to the last snapshot selected regardless row context. 


In the example I want to show only sales where sales date > 06/06/2016 for every snapshot date. 


Is there any way to do that?.


Thanks in advance,




Super User
Super User

Re: last date regardless context

Check out ALL and ALLEXCEPT functions, these remove all context and remove all except selected context respectively.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Angel Member

Re: last date regardless context

Hi, @Greg_Deckler


Many thanks for your quick response.


I have tried do it in different ways whithout desired result.


In the image below, there is three dates.


MaxDate 1 = CALCULATE(MAX('Dimdate'[Snapshot date]);ALL('Dimdate'))
MaxDate 2 = CALCULATE(MIN('Dimdate'[Snapshot date]);ALLEXCEPT(Dimdate;Dimdate[WeekCount]))
MaxDate 3 = CALCULATE(MIN('Dimdate'[Snapshot date]);ALL(Dimdate[WeekCount]) )


However, measures don't return max date I need. In this case It has to be 23/05/16 for every row context.



If I choose week count from 340 to 342, it has to be 30/05/16 for every snapshot date.




Any tip?



v-ljerr-msft Super Contributor
Super Contributor

Re: last date regardless context

Hi @Angel,


Based on my understanding, I think the formula below should work in your scenario.

MaxDate =
MAX ( 'Dimdate'[Snapshot date] );
ALLEXCEPT ( Dimdate; Dimdate[WeekCount] )


MaxDate = CALCULATE ( MAX ( 'Dimdate'[Snapshot date] ); ALL ( Dimdate[Snapshot date] ) )

If that is not the case, could you post your table structures with some sample data? So that we can help further investigate on the issue. It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.Smiley Happy



Angel Member

Re: last date regardless context

Hi, @v-ljerr-msft


It works!!!!!!.... Thank you, master!!!!!


The last thing I want to do is only consider sales where sales date > max fecha. To do that, I have tried with this phormula..

Sales amount after max snapshot = CALCULATE(MAX('Sales registered'[Sales amount]);FILTER('Sales registered';'Sales registered'[Sales Date] > [MaxDate])) 

However, as you can see in the image above It doesn´t work...




Any tip?


Angel Member

Re: last date regardless context

Hi, @v-ljerr-msft


As you have said before, I include a link with pbix file.


The goal is only consider sales where sales date > last snapshot date selected in slicers.


Many thanks for your help,