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
Hascins
Frequent Visitor

Values over time

Hi,

 

I have a table that basically looks like this:

http://imgur.com/a/jcaxE

 

Now I would like to create a measure that calculates the differences in value over time. See column Change

http://imgur.com/a/dey2a

 

The date of the table above is connected to a datetable just in case this could be a part of the solution that looks like this:

 

Date =
 ADDCOLUMNS (
 CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"Today";today();
"Yesterday";today()-1;
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
 "Year"; YEAR ( [Date] );
 "Monthnumber"; FORMAT ( [Date]; "MM" );
 "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
 "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
 "MonthNameShort"; FORMAT ( [Date]; "mmm" );
 "MonthNameLong"; FORMAT ( [Date]; "mmmm" );
 "DayOfWeekNumber"; WEEKDAY ( [Date] );
 "DayOfWeek"; FORMAT ( [Date]; "dddd" );
 "DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
 "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
 "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
 "Heute -1 ";if([date]>=NOW()-2;"Y"; "N");
 "Heute -2";if([date]>=NOW()-3;"Y"; "N");
"Date-1";[Date]-1   )

 

I tried a lot of things but i cant find a proper solution for this. I would really appreciate it if someone could help!

 

 

 

1 ACCEPTED SOLUTION

Hi @Hascins,

 

Based on my test, you should be able to follow steps below to get your expected result. Smiley Happy

 

1. Add an Index column to your table in Query Editor under Add Column tab.

 

index1.PNG

 

2. Then you should be able to use the formula below to create a new calculate column in your table.

Change =
Table1[Value]
    - CALCULATE (
        MAX ( Table1[Value] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Index]
                = EARLIER ( Table1[Index] ) - 1
                && Table1[Product] = EARLIER ( Table1[Product] )
        )
    )

c1.PNG

 

Regards

View solution in original post

3 REPLIES 3
Hascins
Frequent Visitor

If this was asked before pls give me a pointer...i didn't find anything that actually helped

Hi @Hascins,

 

Based on my test, you should be able to follow steps below to get your expected result. Smiley Happy

 

1. Add an Index column to your table in Query Editor under Add Column tab.

 

index1.PNG

 

2. Then you should be able to use the formula below to create a new calculate column in your table.

Change =
Table1[Value]
    - CALCULATE (
        MAX ( Table1[Value] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Index]
                = EARLIER ( Table1[Index] ) - 1
                && Table1[Product] = EARLIER ( Table1[Product] )
        )
    )

c1.PNG

 

Regards

Thank you very much!

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.