Last Week vs This Week Comparison

[rkaushik]

I have following fields in a table "OppHistory":

• OppId - Unique idenfier for opp
• Edit Date - Date when edits were made on Opp Id
• Stage Old Value - The previous stage of Opp Id
• Stage New Value - The new Stage of the Opp Id
  
  Here's the sample data:

OppId	Edit Date	Stage OldValue	Stage NewValue	Amount
AA	11/18/2023 10:50	5	1	100
AA	11/14/2023 16:11	4	5	100
BB	11/20/2023 5:40	3	4	123
BB	11/6/2023 5:22	1	3	123
CC	11/18/2023 10:43	4	1	500
CC	11/17/2023 12:46	1	4	500

For each Opp Id, I want to check what was the stage for the latest date from beginning of data till end of last week ( Friday to Wednesday) and what is it this week ( Friday to Wednesday) for the latest date and return the amount.

This is what I want the output to be like:

Stage	Last Week	This Week
1		600
2
3	123
4	500	123
5	100

Stage is a column from another table "Opp" which is connected to OppHistory with a one to many relationship via OppId. Stage just displays the current value of an OppId

From the output table, Stage 1 has amount for this week = 600 because the stage this week for AA and CC is 1. Similarly for Stage 4, output is 123 amount since this week for opp BB, the stage changed from 3 to 4 which is why for last week it was at stage 3. 

Would it be possible to create a measure to get the expected output above? 
Please let me know if the information I have provided is sufficient.
Thanks in advance

[sergej_og]

You can try to play around with the OFFSET() formula.
Cool one to solve week-over-week comparisons.

Ragards

[AnalyticPulse]

Not sure if this will solve your issue but you can get some perspective from this answer:
To achieve the desired output in Power BI, you can create a measure using DAX (Data Analysis Expressions). Below is an example measure that you can use:

OppHistoryLastWeekThisWeek =
VAR LastWeekStart = TODAY() - WEEKDAY(TODAY(), 2) - 6 - 7
VAR LastWeekEnd = TODAY() - WEEKDAY(TODAY(), 2) - 7
VAR ThisWeekStart = TODAY() - WEEKDAY(TODAY(), 2) + 1
VAR ThisWeekEnd = TODAY() - WEEKDAY(TODAY(), 2)

RETURN
SUMMARIZE (
OppHistory,
OppHistory[Stage OldValue],
"Last Week",
CALCULATE (
SUMX (
FILTER (
OppHistory,
OppHistory[Edit Date] >= LastWeekStart
&& OppHistory[Edit Date] <= LastWeekEnd
),
OppHistory[Amount]
)
),
"This Week",
CALCULATE (
SUMX (
FILTER (
OppHistory,
OppHistory[Edit Date] >= ThisWeekStart
&& OppHistory[Edit Date] <= ThisWeekEnd
),
OppHistory[Amount]
)
)
)

This measure calculates the sum of amounts for each stage for the last week and this week. It uses the SUMMARIZE function to create a table that includes the distinct values of the "Stage OldValue" field and calculates the sum of amounts for the specified date ranges.

Make sure to replace "OppHistory" with the actual name of your table if it's different. You can then use this measure in a table or matrix visualization in Power BI to display the desired output. The measure creates columns for "Last Week" and "This Week" amounts for each stage.

If this helped, Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
https://instagram.com/analytic_pulse
https://analyticpulse.blogspot.com/

subscribe to Youtube channel For fun facts:
https://www.youtube.com/@CogniJourney