last date regardless context

[Angel]

Hi, everyone

Please, help!!!!!

 

The matrix is composed by:

  Rows:

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

        Rel.Week --> Relative week respect current one

        Snapshot date --> Snapshot date 

   Columns:

        Sales month --> Month where sales have been occurs

   Values:

        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,

[Greg_Deckler]

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

[Angel]

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?

Thanks!!!!

[v-ljerr-msft]

Hi @Angel,

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

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

Or

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.

Regards

[Angel]

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]

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.

https://noray-my.sharepoint.com/personal/lcorrea_noray_com/_layouts/15/guestaccess.aspx?docid=075a0e...

Many thanks for your help,