cancel
Showing results for
Did you mean:
Resolver III

## 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 IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Resolver III

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

Microsoft

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

Resolver III

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?

Resolver III

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

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.