Forum Discussion
Shifting Values
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())
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
4 Replies
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 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
Hi ajohn1,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
