Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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())
Solved! Go to 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
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
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |