Search code examples
datepowerbimeasurecumulative-sum

How to calculate using the same measure when related date has no data (past and future)?

After trying a lot of approaches and formulas I decided to ask this question.

See this Matrix visual:

enter image description here

WeekDate is column in a table called Planning. It's related to another date time column Week_Imported in another table called Export.

Export table only has values for the dates: 23-Dec-19, 30-Dec-19 and 06-Jan-20 whereas Planning table has dates spanning multiple weeks in the past and future.

Cumulative Plan Count is calculating correctly as long as there are matching dates between the tables Planning and Export.

Now I'd like to keep calculating even when there's no date matching. I want to get the value 32 from the FIRSTDATE which has data (in this case 23-Dec-2019) and backfill the past with 32.

For dates in the future I'd like to use LASTDATE (06-Jan-20) value which is 89.

Something like this:

WeekDate          Cumulative Plan Count
.
.
.
25-Nov-19         32
02-Dec-19         32 
09-Dec-19         32
16-Dec-19         32
23-Dec-19         32 <= First WeekDate which has data [backfill past with 32]
30-Dec-19         57
06-Jan-19         89 <= Last WeekDate which has data [fill future with 89]
13-Jan-20         89
20-Jan-20         89
27-Jan-20         89
.
.
.

The formula used for the cumulative SUM is this:

Cumulative Plan Count = 

CALCULATE (
    ROUNDUP([1 - Target] * MAX(Planning[1 - Plan]), 0)
    ,
    FILTER (
        ALL ( Planning[WeekDate] ),
        Planning[WeekDate] <= MAX(Planning[WeekDate])
    )
)

####### Edit 1 #######

Using this measure below I get 1s for the past...

1 - Target = 

VAR minWeek = MIN(Export[Week_Imported])

VAR targetCount =
CALCULATE (
        COUNT( 'Export'[1 - Plan]),
        FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_")))

var minTarget = CALCULATE (
        COUNT( 'Export'[1 - Plan]),
        FILTER('Export', OR(Export[1 - Plan]="YES", Export[1 - Plan]="_")
        && Export[Week_Imported] = minWeek))

RETURN

SWITCH(TRUE,
targetCount = BLANK(),  1, // Here is where I need to get the 1st row value (32) and fill up the column up above...
targetCount)

The problem is that no matter what I do I can't get the 1st value for 23-Dec-2019 (32) to fill up the Cumulative Plan Count column.

This is the result when I use the formula above:

WeekDate          Cumulative Plan Count
.
.
.
25-Nov-19         1
02-Dec-19         1 
09-Dec-19         1
16-Dec-19         1
23-Dec-19         32 <= First WeekDate which has data
30-Dec-19         57
06-Jan-19         89 <= Last WeekDate which has data
13-Jan-20         89
20-Jan-20         89
27-Jan-20         89
.
.
.

####### Edit 2 #######

I put together a simplified Sample.pbix which shows what I'm trying to accomplish with minimum data to test things: https://drive.google.com/drive/folders/1zxS_2VE9_0JEMXvsg9Dq196BK552RbNo?usp=sharing

This screenshot has more details: https://drive.google.com/open?id=1_-IMEpLwuWWN6vrrT_TNWbeqZ7f1LOan

Solution

  • Let me introduce the solution with intermediate steps.

    In your data schema, Planning and Export tables are in one to many relationship. Planning is in the grain of every week, while Export has more rows for each week.

    Relationship diagram

    On this basis, the measure to count the number of Export rows for each Planning week is as simple as this.

    Plan Count (Basic) = COUNTROWS ( 'Export' )

    When you slice by Planning[WeekDate], this measure returns the counts of Export rows for the corresponding weeks.

    Actually, you need FILTER to count only rows you are interested in.

    Plan Count =
COUNTROWS (
    FILTER (
        'Export',
        OR ( 'Export'[Plan] = "YES", 'Export'[Plan] = "_" )
    )
)

    Here is the result we get so far.

    Result 1

    Having this measure as the starting point, we need to extend the calculation to the periods where the data does not exist. In order to do that, we need to handle the filter context where [Plan Count] is evaluated.

    We need to get the first and final weeks where Export data exists. Here is a formula that returns the first date of the data regardless of the slicer.

    First Data Week =
CALCULATE (
    MIN ( Planning[WeekDate] ),
    REMOVEFILTERS ( Planning[WeekDate] ),
    TREATAS (
        CALCULATETABLE (
            VALUES ( 'Export'[Week_Imported] ), -- Foreign key referencing Planning[WeekDate]
            REMOVEFILTERS ( Planning )
        ),
        Planning[WeekDate]
    )
)

    First Data Week

    We can use this date to modify the filter context to calculate [Plan Count] of the first data week. In fact, below measure always returns 1064, which is the number of [Plan Count] in December 30, 2019.

    First Week Plan Count = 
VAR _FirstDataWeek = CALCULATE (
    MIN ( Planning[WeekDate] ),
    REMOVEFILTERS ( Planning[WeekDate] ),
    TREATAS (
        CALCULATETABLE (
            VALUES ( 'Export'[Week_Imported] ),
            REMOVEFILTERS ( Planning )
        ),
        Planning[WeekDate]
    )
)
RETURN
CALCULATE (
    [Plan Count],
    Planning[WeekDate] = _FirstDataWeek
)

    First Week Plan Count

    Using this technique, we can expand the first and final values of [Plan Count] to the past and the future dates. Below is the final formula, which iterates over Planning table, and apply different filter context to calculate [Plan Count].

    Extended Plan Count = 

-- Planning[WeekDate] values where Export data exists
VAR _DataWeeks = CALCULATETABLE (
    VALUES ( Planning[WeekDate] ),
    REMOVEFILTERS ( Planning[WeekDate] ),
    TREATAS (
        CALCULATETABLE (
            VALUES ( 'Export'[Week_Imported] ), -- Foreign key referencing Planning[WeekDate]
            REMOVEFILTERS ( Planning )
        ),
        Planning[WeekDate]
    )
)

-- First and last Planning[WeekDate] where Export data exists
VAR _FirstDataWeek = MINX ( _DataWeeks, [WeekDate] )
VAR _FinalDataWeek = MAXX ( _DataWeeks, [WeekDate] )

-- [Plan Count] values of first and last weeks
VAR _FirstDataWeekPlanCount = CALCULATE ( [Plan Count], Planning[WeekDate] = _FirstDataWeek )
VAR _FinalDataWeekPlanCount = CALCULATE ( [Plan Count], Planning[WeekDate] = _FinalDataWeek )

RETURN
SUMX (
    Planning,
    SWITCH (
        TRUE,
        Planning[WeekDate] < _FirstDataWeek, _FirstDataWeekPlanCount,
        Planning[WeekDate] > _FinalDataWeek, _FinalDataWeekPlanCount,
        [Plan Count]
    )
)

    Final Result