Cummulative Sum on Matrix not working on month with no records

[MarioPConst]

Greetings,

I have the following scenario: I a single table called Contabilidade with customer documents that i merged from several databases with document spanning several years I trying to diplay the cumulative sum on a matrix grouped by month. The total of a certain document might positive and negative value (because of the type of document) so the cumulative sum it might positive, negative or depending on document of that perticular month

• The columns are the databases, one  for earch year, it will contain the documents of that year and some unpayed documents from previous year wich are in a group called Reabertura
• The Rows are grouped by Customer, Group Month (contains one line for Reabertura e one for earch month) and finnalythe document

I was able to calculate the cumulative sum correctly it does work correcly except for when the there no documents for that month. In the following image all the fields are correct even the empty ones (the negative total of documents of the month cancel the positive value) the only thing wrong is April 2022 in wich there are no records and so it should contain the previous cumulative sum 6.072,44

the measue that I use is:

Cumulative = 
VAR varCustomerID = SELECTEDVALUE(Contabilidade[CustomerID])

return CALCULATE (
    sum(Contabilidade[Total]),
    FILTER (
    ALLSELECTED(Contabilidade),
        Contabilidade[CustomerID ] = varCustomerID
        && Contabilidade[BD] = MAX ( Contabilidade[BD] ) 
        && Contabilidade[Date] <= MAX ( Contabilidade[Date] )  
))

I have tried every single solution that I found on several posts, but none what worked whith this scenario and wit filters (in the image there a visual filter to diplay only the customer).

Can anyone point to a solution that migth work with this ?
Thanks in Advance.

[lbendlin]

You need to use the Month column from the dimension table, not the fact table.

[MarioPConst]

Greetings,

There are many things I don't know about power bi, I assumed when you say dimension table you mean I have to have a table with all the group month values (dimension table?!?) separed from the main table (fact table ?!?), correct ? 

I created and table (called Contabilidade Group Month Number Table)  with one column (Group Month Number) with numeric part of month (0 to 12) and created a relation with the main table (contabilidade) and I tried several methods and I  can't make it work, maybe I missing somehting.

The formula that I am using it 

Cumulative = 
VAR varCustomerID = SELECTEDVALUE(Contabilidade[CustomerID])
VAR varGroupMonthNumber = SELECTEDVALUE(Contabilidade Group Month Number Table[Group Month Number])


return CALCULATE (
    sum(Contabilidade[Total]),
    FILTER (
    ALLSELECTED(Contabilidade Group Month Number Table),
         SELECTEDVALUE(Contabilidade[CustomerID]) = varCustomerID
         && SELECTEDVALUE(Contabilidade[BD]) <= MAX ( Contabilidade[BD] ) 
         && SELECTEDVALUE(Contabilidade[Group Month Number]) <= varGroupMonthNumber 
))

• I replaced the group month in the matrix with the group month number of dimension table
• I noticed and I get this result without the  SELECTEDVALUE(Contabilidade[CustomerID]) = varCustomerID and  SELECTEDVALUE(Contabilidade[BD]) <= MAX ( Contabilidade[BD] )  part of the filter
  
  Result

 

[lbendlin]

Your data model is supposed to include a calendar table. That table includes all dates and the associated months, quarters, years etc.  That's your date dimension. 

Now when you want to show data by month you pick that month column from the date dimension table (calendar table) and not from the fact table that may also include a month column. (Better to hide that one).  Of course for all of this to work properly your fact table also needs to include at least one date column so you can wire your data model together.

[MarioPConst]

I already have a calendar date anda and is connected to the fact table and I am using a visual filter on the page to limit the minimum and maximum date for that page.

I can't use that calendar month column because of the group 0 (Reabertura) because it contains unpayed documents from the previous years (which migth contain several months from previous year) and need to keep them seperated like I have on the image.

To solve this I use the column Group Month Number with has goes between 0 and 12 where 0 the unpayed documents from the previous I mentioned.

After your previous reply I created a table with only those values, made a relation between those 2 tables, replaced the month group column of the main table with the column from this new table and tried to changed measure but only got that strange result I mentioned on my previous post.

From both your replies I understood the ideia, but I can't seem to implement the measure correclyt, the diplayed total is correct for each database (year) but no the month.