Forum Discussion

Angel's avatar
Angel
Resolver III
9 years ago

last date regardless context

Hi, everyone

 

Please, help!!!!!

 

 

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,

 

 

 

Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

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

    • Angel's avatar
      Angel
      Resolver III
      9 years ago

      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.

       

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

       

       

      Any tip?

       

      Thanks!!!!

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

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

         

        Regards