Cumulative Values for missing period in a 2x2 matrix

[mbudmx]

Hi, I am fairly new to powerbi. 

I have the following table - 

date map2 va
01-Jan-24 ca 5
01-Feb-24 ca 10
01-Mar-24 ca 14
01-Jan-24 cl 23
01-Mar-24 cl 21
01-Apr-24 cl 54

i want to populate cumulative values for each of the month  as show in the table below

map2 jan feb mar apr
ca 5 15 29 29
cl 23 23 44 98
Total 28 38 73 127

However, my current dax query doesn't work when there are missing values - 

Cumulative Value =

VAR CurrentDate = MAX('Table5'[date])

VAR CurrentMap2 = MAX('Table5'[map2])

RETURN

CALCULATE(

    SUM('Table5'[va]),

    FILTER(

        ALL('Table5'[date]),

        'Table5'[date] <= CurrentDate

    ),

    'Table5'[map2] = CurrentMap2

)

The output is this. Also, the totals are incorrect. 

map2 jan feb mar apr
ca 5 15 29
cl 23 44 98
Total 23 15 44 98

i am unable to get it to work. please help.

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar Table with calculated column formulas of Year, Month name and Month number.  Sort the Month name column by the Month number
2. Create a relationsh (Many to One and Single) from the Date column of the Fact Table to the Date column of the Calendar Table
3. To your matrix visual, drag Year and Mont name from the Calendar Table
4. Write these measures

Total = sum('Table5'[va])

Running total = calculate([Total],datesytd(calendar[date],"31/12"))

Hope this helps.

[Sahir_Maharaj]

Hello @mbudmx,

Can you please try this approach:

1. Create a Date Table

DateTable = CALENDAR(MIN('Table5'[date]), MAX('Table5'[date]))

2. Update the DAX formula

Cumulative Value = 
VAR CurrentDate = MAX('DateTable'[Date])
VAR CurrentMap2 = MAX('Table5'[map2])
VAR Result = 
    CALCULATE(
        SUM('Table5'[va]),
        FILTER(
            ALL('DateTable'[Date]), 
            'DateTable'[Date] <= CurrentDate
        ),
        'Table5'[map2] = CurrentMap2
    )

RETURN
IF(
    ISINSCOPE('Table5'[map2]), 
    Result, 
    SUMX(
        SUMMARIZE(
            'Table5',
            'Table5'[map2],
            "CumulativeForMap2", [Cumulative Value]
        ), [CumulativeForMap2]
    )
)

Hope this helps.