Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated column filtered by two columns value

Hi,

I have this calculated table, it has rows with values, colums with date of creation truncated to weeks and a check in value.

I need another colum with the value of the last week (creation week - 7 days) and check in date last year.

I´ve tryed several solution with out acurrate result, what will be the best aproach?

Thanks!

 
 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try :

    New Column = sumx(filter(Table,[Creation] = (earlier([creation]) - 7) && [Check in] = (earlier([check in]) - 365)),[room nights])

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Not 100% on what you are asking with that last one. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

     

    If you want the value of the last week you can do this:

     

    Column =
  VAR __Current = [CheckIn]
  VAR __CurrentMinus7Days = (__CurrentWeek - 7) * 1.
RETURN
  MAXX(FILTER('Table',[Checkin] = __CurrentMinus7Days),[RoomNights])
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for your help, but this does not work.

      I just wanted to put the same value of last year creation and last year check in, trys to use your solution with out luck:

      LastYearCreationsOverlastYearCheckin =
      VAR __Creation = [Creation]
      VAR __CreationMinus365Days = ( __Creation - 365) * 1
      VAR __CheckIn = RoomNights[CheckIn]
      VAR __CheckInMinus365Days = ( __CheckIn - 365) * 1
      RETURN
      MAXX(FILTER(RoomNights;(RoomNights[Creation] = __CreationMinus365Days && RoomNights[CheckIn] = __CheckInMinus365Days));[RoomNights])
      I want the same value of creation - 365 day and the same value of checkin - 365 day
      Validating this data against SQL does not work.
      Can you please help me and thanks!!!!
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Did not get it completely

    New column = [creation date]-7

     

    The other I did not get

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi

      Thanks frist of all!

      New Colum = sum(room nights) where Creation = creation - 7 and Check in = check in - 365

      In SQL it´s easy 🙂

      Cheers

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Try :

        New Column = sumx(filter(Table,[Creation] = (earlier([creation]) - 7) && [Check in] = (earlier([check in]) - 365)),[room nights])