Need help in DAX to calculate Previous year data

[Anonymous]

Hi Team,  @tamerj1

Thanks for helping in the below mentioned question.

Solved: Re: Need Help with DAX Command - Microsoft Power BI Community

below is the formula which is working fine .
CYCQCW-Win Rev =
VAR CurrentFCST = MAX ( 'Trend'[FCST] )
VAR CurrentWeek = MAX ( 'Trend'[WEEK_NUM] )
VAR CurrentStage = MAX ( 'Trend'[SS_No])
VAR CurrentValue = MAX ( 'Trend'[REV$M] )
VAR MaxWeek = CALCULATE ( MAX ( 'Trend'[WEEK_NUM] ), ALLEXCEPT ( 'Trend', 'Trend'[FCST] ) )
RETURN
IF (
CurrentFCST = "CQ" && CurrentWeek = MaxWeek && CurrentStage IN { "07", "08" },
CurrentValue,
0
);

Requirement : To calulate previous year CQ data so that we can create a yty .

data set :

FCST	WEEK	STAGE	REVENUE
CQ	4	07	200
NQ	8	09	100
CQ	4	08	50
PYCQ	4	07	30
PYCQ	10	08	60
PYCQ	4	08	90

So my expectation is i have to filter fcst = pycq and stage = 07 , 08  and max of week where fcst = cq in this case 4 and update the vlaue in new column rest all will be 0 , in above case row 4 will give me a value of 30 and row  will give me a value 90 , rest all will be 0 . 
reason : I have to calculate yty,  in order to calculate yty i have to get previous year same qtr same week data so that we are comparing evenly . you can use the above formula to modify.

Thanks & Regrards
Shiv

[tamerj1]

Hi @Anonymous 

if you are looking for a measure the

CYCQCW-Win Rev =
VAR CurrentFCST =
    MAX ( 'Trend'[FCST] )
VAR CurrentWeek =
    MAX ( 'Trend'[WEEK_NUM] )
VAR CurrentStage =
    MAX ( 'Trend'[SS_No] )
VAR CurrentValue =
    MAX ( 'Trend'[REV$M] )
VAR MaxWeek =
    CALCULATE ( MAX ( 'Trend'[WEEK_NUM] ), 'Trend'[FCST] = "CQ" )
RETURN
    IF (
        CurrentFCST = "PYCQ"
            && CurrentWeek = MaxWeek
            && CurrentStage IN { "07", "08" },
        CurrentValue,
        0
    )

For column

CYCQCW-Win Rev =
VAR CurrentFCST =
    'Trend'[FCST]
VAR CurrentWeek =
    'Trend'[WEEK_NUM]
VAR CurrentStage =
    'Trend'[SS_No]
VAR CurrentValue =
    'Trend'[REV$M]
VAR MaxWeek =
    CALCULATE ( MAX ( 'Trend'[WEEK_NUM] ), 'Trend'[FCST] = "CQ" )
RETURN
    IF (
        CurrentFCST = "PYCQ"
            && CurrentWeek = MaxWeek
            && CurrentStage IN { "07", "08" },
        CurrentValue,
        0
    )

[Anonymous]

Hi @tamerj1 ,

Thanks for replying !!

QQ : which week number the Current week = maxweek return based on the above dataset if it is 4 then it is fine , if it is 10 then in that case i am looking for week number as 4. As I want to compare Current week of current year vs the same week of previous year .
Thanks & Regards

Shiv

[tamerj1 · Accepted solution]

Hi @Anonymous 

you are right

code is updated in the previous comment