cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver III

## Day to Day Calculated Column

Hello,

I have the following example dataset:

 Hotel Date Amount A 1/1/2017 100 A 1/2/2017 200 A 1/3/2017 300 B 1/1/2017 400 B 1/2/2017 500 B 1/3/2017 600 A 1/1/2018 700 A 1/2/2018 800 A 1/3/2018 900 B 1/1/2018 1000 B 1/2/2018 1100 B 1/3/2018 1200

What I'd like is a calculated column that computes the previous years amounts on a day to day level. So, instead of matching the 1/1/2018 to 1/1/2017, we are matching day of the week. So, we want to match 1/1/2018 to 1/2/2017, basically we want to subtract 364 days. So this is what I'm looking for;

 Hotel Date Amount DoD A 1/1/2017 100 A 1/2/2017 200 A 1/3/2017 300 B 1/1/2017 400 B 1/2/2017 500 B 1/3/2017 600 A 1/1/2018 700 200 A 1/2/2018 800 300 A 1/3/2018 900 B 1/1/2018 1000 500 B 1/2/2018 1100 600 B 1/3/2018 1200

Does anyone know how to go about doing this?

Thanks!

1 ACCEPTED SOLUTION
Resolver I

Hey @vega!

I was able to use LOOKUP and specify both 364 days ago, and hotel. I added a calculated column to my test data model using the following expression:

```PriorYearHotelValue =
IF(
Sheet1[Hotel] = "A",
LOOKUPVALUE(Sheet1[Value],Sheet1[Date],Sheet1[Date] - 364,Sheet1[Hotel], "A"),
LOOKUPVALUE(Sheet1[Value],Sheet1[Date],Sheet1[Date] - 364,Sheet1[Hotel], "B")
)```

In my test data, I started with three columns: Date, Hotel, & Value. The LOOKUP expression looks for the value in Sheet1[Value], where date matches 364 days ago, and hotel matches A. I only have two hotels, so my IF statement executes the first LOOKUP if matches on A, and the second if not.

Hope this helps!

2 REPLIES 2
Resolver I

Hey @vega!

I was able to use LOOKUP and specify both 364 days ago, and hotel. I added a calculated column to my test data model using the following expression:

```PriorYearHotelValue =
IF(
Sheet1[Hotel] = "A",
LOOKUPVALUE(Sheet1[Value],Sheet1[Date],Sheet1[Date] - 364,Sheet1[Hotel], "A"),
LOOKUPVALUE(Sheet1[Value],Sheet1[Date],Sheet1[Date] - 364,Sheet1[Hotel], "B")
)```

In my test data, I started with three columns: Date, Hotel, & Value. The LOOKUP expression looks for the value in Sheet1[Value], where date matches 364 days ago, and hotel matches A. I only have two hotels, so my IF statement executes the first LOOKUP if matches on A, and the second if not.

Hope this helps!

Resolver III

Thank you, I appreciate it. I was also able to come up with an alternate solution. I'll leave it here in case it helps anyone else out in the future.

```=
VAR previousDate = 'Legacy Trans'[Date]-364
RETURN
CALCULATE(
SUM('Legacy Trans'[Amount]),
ALLEXCEPT('Legacy Trans','Legacy Trans'[Hotel]),
'Legacy Trans'[Dates] = previousDate
)```

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors