Forum Discussion

odl001's avatar
odl001
Icon for Advocate II rankAdvocate 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 list of year on year offer cycles. An offer cycle is a 16-month period, usually starts in Aug and ends in Dec of the following year. It also has different categories like Undergrad, Postgrad, etc. 

The fact table is basically a daily snapshot data of all applications with their corresponding status, application ID, offer cycle ID.

I was able to count year-on-year cycle using the following DAX. However, my struggle is to create a measure for the previous cycle. 

Summarized Applicant Count =
CALCULATE(
    DISTINCTCOUNT(CombinedFacts[Applicant ID]),
    FILTER(
        CombinedFacts,
        CombinedFacts[Phase Count Flag] = 1
    )
)

From the user interface, user need to first select a date (or reporting date), which has a corresponding "Day in Cycle". Day in Cycle means the number of days since the offer cycle start day. In the fact, multiple dates can have similar "Day in Cycle" and since offer cycle start date varies from year on year, it's always not the same exact date.

Here is an example. In this scenario, I should be able to count the current cycle and also the previous cycle based on the same day in cycle.

 

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

11 Replies

  • MattiaFratello's avatar
    MattiaFratello
    Icon for Super User rankSuper User
    1 year ago

    Hi odl001, let me check if I understood correctly.

    Previous cycle should be the previous reporting date which has the same days in cycle?

    • odl001's avatar
      odl001
      Icon for Advocate II rankAdvocate II
      1 year ago

      That is correct MattiaFratello. In my example, 23 May 2024 and 25 May 2025 are both 280th day of the cycle, both in previous and current cycle.

  • odl001's avatar
    odl001
    Icon for Advocate II rankAdvocate 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
      Icon for Super User rankSuper 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
        Icon for Advocate II rankAdvocate 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.

         

  • Elena_Kalina's avatar
    Elena_Kalina
    Icon for Solution Sage rankSolution Sage
    1 year ago

    Hi odl001 

    Please try this one:

    Summarized Applicant Count - Previous Cycle = 
VAR CurrentDayInCycle = 
    MAX(CombinedFacts[days_in_cycle])    
VAR CurrentOfferCategory = 
    VALUES(CombinedFacts[Offer Category])    
VAR CurrentApplicationStatus = 
    VALUES(CombinedFacts[Application Status])    
VAR CurrentOfferCycleID = 
    VALUES(CombinedFacts[Offer Cycle ID])    
VAR CurrentReportingDate = 
    MAX(CombinedFacts[reporting_date])
VAR PreviousReportingDates = 
    CALCULATETABLE(
        VALUES(CombinedFacts[reporting_date]),
        FILTER(
            ALLSELECTED(CombinedFacts),
            CombinedFacts[days_in_cycle] = CurrentDayInCycle &&
            CombinedFacts[reporting_date] < CurrentReportingDate &&
            CombinedFacts[Offer Category] IN CurrentOfferCategory &&
            CombinedFacts[Application Status] IN CurrentApplicationStatus &&
            NOT(CombinedFacts[Offer Cycle ID] IN CurrentOfferCycleID)
        )
    )
    
// Get the most recent previous date
VAR LatestPreviousDate = 
    MAXX(PreviousReportingDates, [reporting_date])
    
RETURN
IF(
    NOT(ISBLANK(LatestPreviousDate)),
    CALCULATE(
        DISTINCTCOUNT(CombinedFacts[Applicant ID]),
        FILTER(
            ALLSELECTED(CombinedFacts),
            CombinedFacts[reporting_date] = LatestPreviousDate &&
            CombinedFacts[days_in_cycle] = CurrentDayInCycle &&
            CombinedFacts[Offer Category] IN CurrentOfferCategory &&
            CombinedFacts[Application Status] IN CurrentApplicationStatus &&
            CombinedFacts[Phase Count Flag] = 1
        )
    ),
    BLANK()
)
  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi odl001 ,

    Try using this DAX:

    Summarized Applicant Count - Previous Cycle =
    VAR CurrentDayInCycle = SELECTEDVALUE(CombinedFacts[days_in_cycle])
    VAR CurrentReportingDate = SELECTEDVALUE(CombinedFacts[reporting_date])
    VAR CurrentStatus = SELECTEDVALUE(CombinedFacts[Application Status])
    VAR CurrentCategory = SELECTEDVALUE(CombinedFacts[Offer Category])
    VAR CurrentCycle = SELECTEDVALUE(CombinedFacts[Offer Cycle ID])

    -- Find the previous offer cycle for the same day in cycle
    VAR PrevCycleDate =
    CALCULATE(
    MAX(CombinedFacts[reporting_date]),
    FILTER(
    ALL(CombinedFacts),
    CombinedFacts[days_in_cycle] = CurrentDayInCycle &&
    CombinedFacts[reporting_date] < CurrentReportingDate &&
    CombinedFacts[Application Status] = CurrentStatus &&
    CombinedFacts[Offer Category] = CurrentCategory &&
    CombinedFacts[Offer Cycle ID] <> CurrentCycle
    )
    )

    RETURN
    CALCULATE(
    DISTINCTCOUNT(CombinedFacts[Applicant ID]),
    FILTER(
    ALL(CombinedFacts),
    CombinedFacts[reporting_date] = PrevCycleDate &&
    CombinedFacts[days_in_cycle] = CurrentDayInCycle &&
    CombinedFacts[Phase Count Flag] = 1 &&
    CombinedFacts[Application Status] = CurrentStatus &&
    CombinedFacts[Offer Category] = CurrentCategory
    )
    )

    Hope this helps!
    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You!

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi odl001 ,
    Just wanted to check if you had the opportunity to review the suggestion provided?
    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi odl001 ,
    Just wanted to check if you had the opportunity to review the suggestion provided?
    Thanks Elena_Kalina  for your prompt response.
    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
    Thank You