Complex sum with CALCULATE

[sanopera]

My team and I are trying to integrate a solution into our PowerBI model for aggregating a table that is filled every time an event is opened.

So an event is recorded and has a record.

Each event has a creation date, a closure date, and a due date.

We are trying to integrate into PowerBI the visualization per day/week/month/year of all the events that are overdue.

The problem we can't solve is the incorrect counting of these events. At the daily view level, the concept we developed works correctly, and it is simply summing up the column. But when switching to aggregated weekly/monthly/annual view, the data gets skewed because the overdue event is repeated more than once because an overdue event can span multiple days.

How can we solve this? Thank you in advance to anyone who can help us.

That's the code that we implemented:

"xxx overdue",COUNTAX(

            FILTER('x',

            'x'[CREATED_TS_OD]<=[Date]

            && [Date]<=(TODAY())

            && OR('x'[CLOSED_TS_OD]>[Date],ISBLANK('x'[CLOSED_TS_OD]))

            && [Date]>'x'[DUE_DATE]

            ),

            TRUE())

[DataNinja777 · Accepted solution]

Hi @sanopera 

In order to do a flexible duration analysis as your required output, we first need to create a calculated calendar table and set it as a disconnected table with your fact table which contains you scenario fields such as

• Creation dates
• Due dates
• Closure dates

For this demonstration, I've created a dummy data and set the dummy closure dates to be after the due dates to indicate overdue dates.  (It may not be all overdue in your real data, so this is just for demonstration.)  

Your data model will look like as shown below:

Then you can write a duration measure like below for the overdue dates, which totally respects your time dimension.  

In this way, dax offers a total flexibility with respect to time dimension, and your overdue dates per events are not double counted and will give you correct snapshot of the status at any given point in time in your calendar table. 

I attach an example pbix file.  

[Gabry]

Not sure to have understood properly but I think you need to use calculated column to solve your problem.

Can't you add a column with a flag, if the row is overdue than flag is yes, if not flag is no, than you just count the yes