Forum Discussion

odl001's avatar
odl001
Advocate II
1 year ago
Solved

Current and Previous Cycle Count Based on Overlapping Cycles

This has been buggling my brain for the past few days. I have the following model, with a single fact table. The Offer Cycle and Offer Cycle Comparison dimension tables are basically just a l...
Data Protection
  • MattiaFratello's avatar
    MattiaFratello
    1 year ago
    Summarized Applicant Count - Previous Period =
CALCULATE(
    DISTINCTCOUNT(CombinedFacts[Applicant ID]),
    FILTER(
        ALL(CombinedFacts),
        CombinedFacts[Phase Count Flag] = 1 &&
        CombinedFacts[days_in_cycle] = MAX(CombinedFacts[days_in_cycle]) &&
        CombinedFacts[reporting_date] = 
            CALCULATE(
                MAX(CombinedFacts[reporting_date]),
                FILTER(
                    ALL(CombinedFacts),
                    CombinedFacts[reporting_date] < MAX(CombinedFacts[reporting_date]) &&
                    CombinedFacts[days_in_cycle] = MAX(CombinedFacts[days_in_cycle])
                )
            )
    )
)
odl001's avatar
odl001
Advocate II
1 year ago

The desired outcome is something like this - if I add the measure in the table, I should see the last reporting cycle's value there MattiaFratello 

  • MattiaFratello's avatar
    MattiaFratello
    Super User
    1 year ago
    Summarized Applicant Count - Previous Period =
CALCULATE(
    DISTINCTCOUNT(CombinedFacts[Applicant ID]),
    FILTER(
        ALL(CombinedFacts),
        CombinedFacts[Phase Count Flag] = 1 &&
        CombinedFacts[days_in_cycle] = MAX(CombinedFacts[days_in_cycle]) &&
        CombinedFacts[reporting_date] = 
            CALCULATE(
                MAX(CombinedFacts[reporting_date]),
                FILTER(
                    ALL(CombinedFacts),
                    CombinedFacts[reporting_date] < MAX(CombinedFacts[reporting_date]) &&
                    CombinedFacts[days_in_cycle] = MAX(CombinedFacts[days_in_cycle])
                )
            )
    )
)
    • odl001's avatar
      odl001
      Advocate II
      1 year ago

      Thanks MattiaFratello 
      It works, however, when I add more dimensions to the table, the outcome remains static. For example, I need to take into account application status, offer category like Undergrad or Postgrad.

       

      • MattiaFratello's avatar
        MattiaFratello
        Super User
        1 year ago

        odl001, can you try with this one?

         

        Summarized Applicant Count - Previous Period =
VAR CurrentDaysInCycle = SELECTEDVALUE(CombinedFacts[days_in_cycle])
VAR CurrentReportingDate = SELECTEDVALUE(CombinedFacts[reporting_date])

VAR PrevReportingDate =
    CALCULATE(
        MAX(CombinedFacts[reporting_date]),
        FILTER(
            ALL(CombinedFacts),
            CombinedFacts[reporting_date] < CurrentReportingDate &&
            CombinedFacts[days_in_cycle] = CurrentDaysInCycle &&
            CombinedFacts[Application Status] = SELECTEDVALUE(CombinedFacts[Application Status]) &&
            CombinedFacts[Offer Category] = SELECTEDVALUE(CombinedFacts[Offer Category])
        )
    )

RETURN
CALCULATE(
    DISTINCTCOUNT(CombinedFacts[Applicant ID]),
    FILTER(
        ALL(CombinedFacts),
        CombinedFacts[Phase Count Flag] = 1 &&
        CombinedFacts[reporting_date] = PrevReportingDate &&
        CombinedFacts[days_in_cycle] = CurrentDaysInCycle &&
        CombinedFacts[Application Status] = SELECTEDVALUE(CombinedFacts[Application Status]) &&
        CombinedFacts[Offer Category] = SELECTEDVALUE(CombinedFacts[Offer Category])
    )
)

         

        The MAX is not context-sensitive across additional dimensions.

        We need to calculate the reporting_date and days_in_cycle per row in the visual, not as a global MAX.