cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Angel
Resolver III
Resolver III

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
Greg_Deckler
Super User IV
Super User IV

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

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?

 

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,

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors