Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I would like to calculate the value of a period earlier but without using a seperate date table.
Instead of a date column, I created a seperate column with a lists quarters (called 'datesortnumber') but they are mentioned as 1, 2, 3, 4, 5, 6 etc.
I would like to display the value whereby the datesort period is one earlier.
This I would like to achieve with a measures as well as through a calculated column.
A very simplified version of my input table:
VALUE, DATESORTNUMBER
111, 1
112, 2
113, 3
114, 4
115, 5
The result should look like:
DATESORTNUMBER, RESULTVALUE
1, no result (because no value at datesortnumber 0)
2, 111
3, 112
4, 113
5, 114
How can I achieve to create this result value column whereby the value is one datesort earlier (through a measure and a calculated column? Thank you for the help!
Solved! Go to Solution.
@Anonymous Try:
Measure =
VAR __DateSortNum = MAX('Table'[DATESORTNUMBER])
RETURN
MAXX(FILTER(ALL('Table'),[DATESORTNUMBER] = __DateSortNum - 1),[VALUE])
Column =
VAR __DateSortNum = 'Table'[DATESORTNUMBER]
RETURN
MAXX(FILTER(ALL('Table'),[DATESORTNUMBER] = __DateSortNum - 1),[VALUE])
@Anonymous Try:
Measure =
VAR __DateSortNum = MAX('Table'[DATESORTNUMBER])
RETURN
MAXX(FILTER(ALL('Table'),[DATESORTNUMBER] = __DateSortNum - 1),[VALUE])
Column =
VAR __DateSortNum = 'Table'[DATESORTNUMBER]
RETURN
MAXX(FILTER(ALL('Table'),[DATESORTNUMBER] = __DateSortNum - 1),[VALUE])
Works perfect, thank you!
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |