Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ajohn1
Advocate I
Advocate I

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())

1 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.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

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

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
ajohn1
Advocate I
Advocate I

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

 

OfficeDateYearActualRunning ActualLast Year Actual
A12/1/20172017110
B12/2/20172017230
C12/3/20172017360
D12/4/201720174100
E12/5/201720175150
F12/6/201720176210
G12/7/201720177280
H12/8/201720178360
I12/9/201720179450
J12/10/2017201710550
      
A12/1/2018201811111
B12/2/2018201812233
C12/3/2018201813366
D12/4/20182018145010
E12/5/20182018156515
F12/6/20182018168121
G12/7/20182018179828
H12/8/201820181811636
I12/9/201820181913545
J12/10/201820182015555

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.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

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

 

Regards,

Frank

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi @ajohn1,

 

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

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.