topic Confronting a column value month by month in DAX Commands and Tips

Confronting a column value month by month

Anonymous — Fri, 07 Jun 2024 13:24:11 GMT

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.

Re: Confronting a column value month by month

sjoerdvn — Fri, 07 Jun 2024 15:04:30 GMT

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

Re: Confronting a column value month by month

Anonymous — Fri, 07 Jun 2024 15:26:04 GMT

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.

Re: Confronting a column value month by month

Anonymous — Mon, 10 Jun 2024 10:59:38 GMT

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.