Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Confronting a column value month by month

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 cont...
  • Anonymous's avatar
    Anonymous
    2 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.