Need Target calculation based on running total

[klehar]

Here is my sample data.

I have sorted the running total.

Now I need to get the Target % which is Running Total for each cell/Budget column

Step 2  : I need to compare QoQ at week level if target was met or unmet.

E.g. Week 1 of Q2 compared to Q1 is 12% against 17%. If Week 2 target > Week 2 target of last quarter then Target is Met else Unmet

Quarter	Week	Budget	Actuals	Running Total	Target %	Target Met/Unmet
Q1	W1	300	50	50	17%
Q1	W2	300	24	74	25%
Q1	W3	300	13	87	29%
Q1	W4	300	19	106	35%
Q1	W5	300	21	127	42%
Q1	W6	300	11	138	46%
Q1	W7	300	47	185	62%
Q1	W8	300	25	210	70%
Q1	W9	300	10	220	73%
Q1	W10	300	26	246	82%
Q1	W11	300	22	268	89%
Q1	W12	300	15	283	94%
Q1	W13	300	30	313	104%
Q2	W1	400	47	47	12%	Unmet
Q2	W2	400	28	75	19%	Unmet
Q2	W3	400	21	96	24%	Unmet
Q2	W4	400	28	124	31%	Unmet
Q2	W5	400	25	149	37%	Unmet
Q2	W6	400	16	165	41%	Unmet
Q2	W7	400	44	209	52%	Unmet
Q2	W8	400	36	245	61%	Unmet
Q2	W9	400	13	258	65%	Unmet
Q2	W10	400	20	278	70%	Unmet
Q2	W11	400	23	301	75%	Unmet
Q2	W12	400	37	338	85%	Unmet
Q2	W13	400	40	378	95%	Unmet

How do I write these 2 columns/measures?

[Sahir_Maharaj · Accepted solution]

Hello @klehar,

Can you please try the following:

1. Calculate Target %

Target % = SalesData[Running Total] / SalesData[Budget]

2. Determine Target Met/Unmet

Target Met/Unmet = 
VAR CurrentWeek = SalesData[Week]
VAR CurrentQuarter = SalesData[Quarter]
VAR PreviousQuarterTarget = 
    CALCULATE(
        MAX(SalesData[Target %]),
        FILTER(
            ALL(SalesData),
            SalesData[Week] = CurrentWeek &&
            SalesData[Quarter] = IF(LEFT(CurrentQuarter, 1) = "Q" && LEN(CurrentQuarter) > 1, "Q" & (VALUE(MID(CurrentQuarter, 2, LEN(CurrentQuarter) - 1)) - 1), CurrentQuarter)
        )
    )
RETURN
    IF(
        ISBLANK(PreviousQuarterTarget),
        BLANK(),
        IF(
            SalesData[Target %] > PreviousQuarterTarget,
            "Met",
            "Unmet"
        )
    )