Forum Discussion

skills29's avatar
skills29
Frequent Visitor
3 years ago
Solved

DAX measure based on a value

Hello,

 

I have a table f_project_status which has a column called [Suspended/InExecution] that classifies if a project is suspended or in execution. I'm trying to create a measure to put in a matrix that shows me projects which is in execution in a month and in the previous month this project was suspended. I got this measure here:

Projects_Stopped_InExecution =
VAR PreviousMonth = EOMONTH(MAX(d_calendar[Date]), -1)
RETURN
    CALCULATE(
        COUNTROWS(f_project_status),
        f_project_status[Suspended/InExecution] = "In Execution",
        INTERSECT(
            VALUES(f_project_status[ProjectName]),
            CALCULATETABLE(
                VALUES(f_project_status[ProjectName]),
                PREVIOUSMONTH(d_Calendar[Date])
            )
        )
    )
 
It partially works because this measure also gets projects which is "In Execution" in a month and was also "In Execution" in the previous month, and i just want to projects which is "In Execution" in a month and was only "Suspended" in the previous month.
 
Could someone help me please? Thanks.
Need Help
  • skills29's avatar
    skills29
    3 years ago

    I already solved, for those who might be useful, here's the measure:

    Projects_Suspended_InExecution =
    VAR PreviousMonth = EOMONTH(MAX(d_calendar[Date]), -1)
    VAR Suspended_projects_last_month =
        CALCULATETABLE(
            VALUES(f_status_project[ProjectName]),
            f_status_project[Suspended/InExecution = "Suspended",
            PREVIOUSMONTH(d_calendar[Date])
        )
    VAR InExecutionProjectsNextMonth =
        CALCULATETABLE(
            VALUES(f_status_project[ProjectName]),
            f_status_project[Suspended/InExecution = "InExecution",
            ALL(d_calendar),
            d_calendar[Date]> PreviousMonth && d_calendar[Date] <= EOMONTH(PreviousMonth, 1)
        )

    RETURN
        COUNTROWS(
            INTERSECT(Suspended_projects_last_month, InExecutionProjectsNextMonth)
        )

1 Reply

  • skills29's avatar
    skills29
    Frequent Visitor
    3 years ago

    I already solved, for those who might be useful, here's the measure:

    Projects_Suspended_InExecution =
    VAR PreviousMonth = EOMONTH(MAX(d_calendar[Date]), -1)
    VAR Suspended_projects_last_month =
        CALCULATETABLE(
            VALUES(f_status_project[ProjectName]),
            f_status_project[Suspended/InExecution = "Suspended",
            PREVIOUSMONTH(d_calendar[Date])
        )
    VAR InExecutionProjectsNextMonth =
        CALCULATETABLE(
            VALUES(f_status_project[ProjectName]),
            f_status_project[Suspended/InExecution = "InExecution",
            ALL(d_calendar),
            d_calendar[Date]> PreviousMonth && d_calendar[Date] <= EOMONTH(PreviousMonth, 1)
        )

    RETURN
        COUNTROWS(
            INTERSECT(Suspended_projects_last_month, InExecutionProjectsNextMonth)
        )