Current and Previous Cycle Count Based on Overlapping Cycles

[odl001]

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.

[v-sdhruv]

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

[Elena_Kalina]

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]

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]

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!

[odl001]

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 · Accepted solution]

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]

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]

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

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.

[MattiaFratello]

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]

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.