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 matr...
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)
        )
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)
    )