Data accumulation / by Quarter with categories

Due to confidentiality, I must not share the original data!

Problem:

There is are two given tables, consisting of the following columns:

Table “FACT_EventsByDate”

EventDate as Date (but not continuous, meaning there is not a date for each day)

EventCompletedDate as Date (see above)

User as Text

Event as Text

EventCompleted as Text (“Completed”; “Uncompleted”)

Segment as Text

EventResult as Integer

Table “DIM_Calendar”

GregorianDate as Date (each date from Jan 01, 2020 to Dec 31, 2027)

Quarter as text (“2020 Q1”

Month as text (“2020 01”)

Week as text (“2020 W01”)

We want to calculate two cumulative values:

1. The distinct total number of Users attended an Event until (“<=”) the reported EventDate
2. The total sum of “EventResult”, completed until (“<=”) the reported EventDate

Trial:

1. Measure:

MSR_NumberOfUsers =

VAR CurrentDate = MAX(DIM_Calendar[GregorianDate])

 RETURN

CALCULATE(

    DISTINCTCOUNT(FACT_EventsByDate [User]),

    FILTER(

        ALLSELECTED(DIM_Calendar),

        DIM_Calendar[GregorianDate] <= CurrentDate

    )

)+0

2. Measure:

MSR_EventResultCumulated =

VAR CurrentDate = MAX(DIM_Calendar[GregorianDate])

RETURN

    CALCULATE(

        SUM(FACT_EventsByDate[EventResult]),

        FILTER(

            ALL(DIM_Calendar),

            DIM_Calendar[GregorianDate] <= CurrentDate

        ),FACT_EventsByDate[EventCompleted] = "Completed"

    )+0

We want to use the measure to create a visual with

Mixed visual (stacked columns and line) by date hierarchy (Year, Quarter)

• MSR_NumberOfUsers Stacked columns by Segment as columns à 1. Y-Axis
• MSR_EventResultCumulated à line 2. Y-Axis
• GregorianDate à X-Axis
• Segment à Legend

As the Dates columns are not continuous due to missing attempts, we used the DIM_Calendar[GregorianDate] which is connected to both (one doted connection).

Target (Mock Up):

However, the result we achieved in PBI looked like this:

The line is also not continuously increasing.

Does anyone have an idea how to solve this problem?