cancel
Showing results for
Did you mean:
Highlighted
Member

## last date regardless context

Hi, everyone

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?.

5 REPLIES 5
Super User

## Re: last date regardless context

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

Proud to be a Datanaut!

Member

## Re: last date regardless context

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!!!!

Super Contributor

## Re: last date regardless context

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

Member

## Re: last date regardless context

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?

Member

## Re: last date regardless context

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...