Forum Discussion
Confronting a column value month by month
- Anonymous2 years ago
Hi Guys!
Yesterday I found the solution, just in case someone has a similar problem, I'll leave it here:
EVALUATE
VAR Table =
FILTER(
SUMMARIZE(
'Demographic Data',
'Demographic Data'[Flow Date],
'Demographic Data'[ID],
'Demographic Data'[Full Name],
'Demographic Data'[Contract Type]),
[Flow Date] > DATE(2022,11,30) &&
[Flow Date] < DATE(2024,01,31))VAR Prev_Contract =
ADDCOLUMNS (
Table,
"Prev_Contract",
VAR ID = [ID]
VAR Date = [Flow Date]
VAR Prev_Date = EOMONTH(Date, -1)
RETURN
CALCULATE (
MAX('Demographic Data'[Contract Type]),
FILTER (
'Demographic Data',
'Demographic Data'[ID] = ID &&
[Flow Date] = Prev_Date)))VAR Contract_Change =
ADDCOLUMNS (
Prev_Contract,
"Contract Change",
IF ( [Contract Type] <> [Prev_Contract], "Y", "N"))
VAR Filtered_Change =
FILTER(
Contract_Change,
[Contract Change] = "Y" &&
[Prev_Contract] <> BLANK())RETURN
SELECTCOLUMNS (
Filtered_Change,
"ID", [ID],
"Employee", [Full Name],
"Previous Contract", [Prev_Contract],
"Current Contract", [Contract Type],
"Contract Change", [Contract Change],
"Change Date", FORMAT([Flow Date], "DD/MM/YYYY"))ORDER BY [Change Date] Asc
It works and gives the desired output, but could be made a little bit more efficient syntax wise.
Can you give an example or mockup of the output you want to create?
Something similar to this:
The goal is to take the initial table,compare each month with its predecessor regarding the contract type, and when the contract type is different between two months we are comparing, output the first month with the new type.