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,
Check out ALL and ALLEXCEPT functions, these remove all context and remove all except selected context respectively.
Proud to be a Datanaut!
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.
Based on my understanding, I think the formula below should work in your scenario.
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.
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...
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,