Forum Discussion

OttoKok's avatar
OttoKok
New Member
2 years ago
Solved

Cumulative amount in missing month

Hi everybody, please help.

 

I have a table with warehouses and movements for individual months.

 

I have a matrix with measure in the report:

CumulatedAmount = IF(COUNTROWS('Warehouses')>0,CALCULATE(SUM(Warehouses[Amount]),all(Warehouses[YearMonth]),DATESBETWEEN(Warehouses[YearMonth],date(2023,01,01),EOMONTH(SELECTEDVALUE(Warehouses[YearMonth]),0))))
 
The problem is that I don't know how to solve to get the value from the last valid month if there is no movement for a certain month.

Can anybody help with this maybe easy problem? 🙂

Thank you.

 

Source file (pbix): https://file.io/LRnu50n2tFnW

 

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Ooops. Sorry. I missed to add MAX in the formula. It should have been:

    Cumulative =
CALCULATE (
    SUM ( Warehouses[Amount] ),
    FILTER (
        ALL ( YearMonth ),
        YearMonth[YearMonth] <= MAX ( YearMonth[YearMonth] )
    )
)

     

7 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi OttoKok ,

     

    Can you please post a link to OneDrive, Google Drive or DropBox? Your link seems to be broken.

     

      • danextian's avatar
        danextian
        Super User
        2 years ago

        Hi OttoKok ,

        If you want to get the cumulative total from 2023-01 up to the current month for each warehouse, create a separate dates table and use that to create time intelligence measure.

        YearMonth = 
DISTINCT(Warehouses[YearMonth]) //separate table

        Create a relationship from that column to your date column in Warehouses and create this measure

        Cumulative = 
CALCULATE (
    SUM ( Warehouses[Amount] ),
    FILTER ( ALL ( YearMonth ), YearMonth[YearMonth] <= YearMonth[YearMonth] ),
    REMOVEFILTERS ( Warehouses[YearMonth] )
)

        Make sure to use the column from YearMonth in your visual.

        Please see attached sample pbix