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

    • odl001's avatar
      odl001
      Advocate II
      1 year ago

      Hi MattiaFratello 
      Not working.
      Also technically, I shouldn't have a slicer for the Day in Cycle here. Just to demonstrate I've got two reporting periods involved.