Confronting a column value month by month

[gianlucaxx95]

Hi forum!

I'm fairly new to Dax and Dax Studio and I just got stuck with a problem I can't seem to understand.

I find myself with a table containing 3 main columns:

• The data column, which contains the last day of each month for each year, like 2023/01/31,2023/02/28 and so on.
• The Code column, which contains an alphanumeric code representing a person.
• The contract code, containing one of two values representing the type of contract of the person.

Here a simplified example:

I should compare the contract code month by month in order to find the date where the contract code was changed.

I know how to compare two time periods, like 2022 and 2023 for example, but I don't know how to make it  among 13 months( the 12 from 2023 + 1 from 2022/31/12). Moreover differently from this example the rows are not in order and there are many more codes representing people.

Thanks in advance to anyone who can give a heads up on how to approach this problem.

[gianlucaxx95 · Accepted solution]

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.

[sjoerdvn]

Can you give an example or mockup of the output you want to create?

[gianlucaxx95]

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.