cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Angel Member
Member

last date regardless context

Hi, everyone

 

Please, help!!!!!

 

Captura.JPG 

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,

 

 

 

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

Captura.JPG

 

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

 

Captura2.JPG

 

Any tip?

 

Thanks!!!!

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 =
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.Smiley Happy

 

Regards

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

 

Captura.JPG

 

Any tip?

 

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

 

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

 

Many thanks for your help,