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 ...
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])

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