Calculated column filtered by two columns value

[JuanviWebbeds]

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!

[amitchandak]

Did not get it completely

New column = [creation date]-7

The other I did not get

[JuanviWebbeds]

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 · Accepted solution]

Try :

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

[Greg_Deckler]

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

[JuanviWebbeds]

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