Data accumulation / by Quarter with categories

[Lalü]

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?

[Lalü]

Hello everybody, Thank you for your help. Due to the holidays in Europe, I have not yet had a chance to check the solutions.

[v-echaithra]

Hi @Lalü ,

Thank you for the update. We understand the delay due to the holidays.

When you have a chance, could you please review the solution and guidance shared. Please let us know whether this solution helps resolve the issue, or if you’re still facing challenges. If needed, you may also share a small, anonymized sample dataset to help us investigate further.

Looking forward to your update.

Best regards,
Thank you.

[v-echaithra]

Hi @Lalü ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @Lalü ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @tharunkumarRTK @lbendlin for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?If not can you please share the sample data as requested by @lbendlin ,so that it will be helpful for us to solve the issue.

[tharunkumarRTK · Accepted solution]

@Lalü 

I guess for 2023 Qtr 1 and 2025 Qtr 4 there is no data in your fact table. Even in this scenario, as per the cumilative logic, data should be displayed in those quarters. I believe there is an issue with your dax forumla.

While writing DAX expressions never filter table, always filter columns. May be modify your dax expressions a bit and filter the columns not tables.

Just to a give you a very simple exmple, I have taken a data sample with discrete dates (fact table)

Created a date table with continuous dates with no missing dates in between (make sure your date table does not have any missing dates in between)

I joined these tables by creating a one to many relation. 

I created a simple dax measure like this 

Cumulative = 
Var __Date = MAX(DateTable[Date])
Var __Result = CALCULATE(SUM('DataTable'[Value]), DateTable[Date] <= __Date, REMOVEFILTERS(DateTable))
RETURN __Result 

and placed it in y axis of a line chart and then placed quarter column in x axis of a line chart  

Hope this helps

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Lalü]

Hi Tharunkumar RTK!
Thank you! That is what I tried and it worked.
Thank you!
Regards, Lars

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...