Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table that basically looks like this:
Now I would like to create a measure that calculates the differences in value over time. See column Change
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!
Solved! Go to Solution.
Hi @Hascins,
Based on my test, you should be able to follow steps below to get your expected result.
1. Add an Index column to your table in Query Editor under Add Column tab.
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] ) ) )
Regards
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.
1. Add an Index column to your table in Query Editor under Add Column tab.
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] ) ) )
Regards
Thank you very much!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |