Shifting Values

[ajohn1]

So I'm trying to create a calculated column/measure [Last Year Values] that will grab 2017 values. That value is only stored when year is equal to 2018. See below.

Last Year Values =
var LYA = CALCULATE([Running Total], Query1[Year] = 2017)
    return if(Query1[Year] = 2018, LYA, 0)

If this helps, [Running Total] and [Actual] is a calculated measure. [MonthCurrent] is a calculated column.

Running Total =
CALCULATE (
    [Actual CI],
    FILTER (
        ALLEXCEPT (Query1, Query1[Year]),
        Query1[MonthCurrent] <= SELECTEDVALUE ( Query1[MonthCurrent] )
    )
)

Actual =
CALCULATE(SUM('Query1'[value]), 'Query1'[DateType] IN { "History" })

MonthCurrent =
var vMonthCurrent = MONTH(Query1[TargetDate])
return if(Query1[DateType] = "History", vMonthCurrent, BLANK())

[ajohn1]

Here is an example of what I'm trying to accomplish. I hope I'm making sense.

Office	Date	Year	Actual	Running Actual	Last Year Actual
A	12/1/2017	2017	1	1	0
B	12/2/2017	2017	2	3	0
C	12/3/2017	2017	3	6	0
D	12/4/2017	2017	4	10	0
E	12/5/2017	2017	5	15	0
F	12/6/2017	2017	6	21	0
G	12/7/2017	2017	7	28	0
H	12/8/2017	2017	8	36	0
I	12/9/2017	2017	9	45	0
J	12/10/2017	2017	10	55	0
A	12/1/2018	2018	11	11	1
B	12/2/2018	2018	12	23	3
C	12/3/2018	2018	13	36	6
D	12/4/2018	2018	14	50	10
E	12/5/2018	2018	15	65	15
F	12/6/2018	2018	16	81	21
G	12/7/2018	2018	17	98	28
H	12/8/2018	2018	18	116	36
I	12/9/2018	2018	19	135	45
J	12/10/2018	2018	20	155	55

[v-frfei-msft · Accepted solution]

Hi @ajohn1,

Based on my test, we can take the following steps to meet your requirement.

1. Create a calendar table using the formula and create relationship between date table and the fact table.

dimtim = CALENDARAUTO()

2. Create the measures as below.

Running Total = TOTALYTD(SUM(Query1[Actual]),dimtim[Date])

Last Year Values = IF(MAX(Query1[Year])=2018,TOTALYTD(SUM(Query1[Actual]),DATEADD(dimtim[Date],-1,YEAR)))

3.Then we can get the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/er544b2ousnulgm/Shifting%20Values.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @ajohn1,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[ajohn1]

Haven't had a chance to apply it yet. I've been busy on other reports. But as soon as I'm able to get back to it, I will let you know the outcome.