Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
botaac
Frequent Visitor

Bring in previous months values that are zero in the current month

I have data that is brought in via monthly reports like so:

DateIDNameAmount_Held
09-20241Cust_1100
09-20242Cust_2100
09-20243Cust_3200
10-20242Cust_2300
10-20243Cust_3400
10-20244Cust_4500
10-20245Cust_5500

 

I would like to create columns that bring in the amounts from the previous amounts. I have been able to successfully do this, but the problem is that it doesn't not bring in records that don't have amounts in the latest month. So, for example, the first row would not show if I created a table visual that pulls the latest report date and would not capture the total previous amount accurately.

 

Example of what I would want:

DateIDNameAmount_Held

Previous_Amount

10-20241Cust_10100
10-20242Cust_2300100
10-20243Cust_3400200
10-20244Cust_45000
10-20245Cust_55000
1 ACCEPTED SOLUTION

Hi @botaac, thank you for your feedback.

 

Can you please try this alternative:

 

1. Create a CustomerDateTable (to include all unique combinations of customers and Year-Months)

CustomerDateTable =
CROSSJOIN(
    DISTINCT('MainTable'[ID]),
    DISTINCT(DateTable[YearMonth])
)

2. Create a FinalTable (to ensure that if a customer was missing from a month, their amount for that month shows as zero)

FinalTable = 
ADDCOLUMNS(
    CustomerDateTable,
    "Date", MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date]),
    "Name", LOOKUPVALUE('MainTable'[Name], 'MainTable'[ID], CustomerDateTable[ID]),
    "Amount_Held", COALESCE(
        LOOKUPVALUE('MainTable'[Amount_Held], 'MainTable'[ID], CustomerDateTable[ID], 'MainTable'[Date], 
                    MAXX(FILTER('MainTable', 'MainTable'[Date] = EOMONTH(DateTable[Date], 0)), 'MainTable'[Date])
        ),
        0
    ),
    "Previous_Amount", 
        VAR CurrentMonth = MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date])
        RETURN COALESCE(
            CALCULATE(
                SUM('MainTable'[Amount_Held]),
                FILTER(
                    'MainTable',
                    'MainTable'[ID] = CustomerDateTable[ID] &&
                    'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
                )
            ),
            0
        )
)

Hope this helps 🙂


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

3 REPLIES 3
Sahir_Maharaj
Super User
Super User

Hello @botaac,

 

Can you please try the following:

 

1. First, ensure Your Data Model Has a Date Table

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
    "YearMonth", FORMAT([Date], "YYYYMM"),
    "Year", YEAR([Date]),
    "Month", MONTH([Date])
)

2. Create a measure that retrieves the previous month’s amount for each customer

Previous_Amount = 
VAR CurrentMonth = MAX('MainTable'[Date])
RETURN 
    CALCULATE(
        SUM('MainTable'[Amount_Held]),
        FILTER(
            'MainTable',
            'MainTable'[ID] = EARLIER('MainTable'[ID]) &&
            'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
        )
    )

3. Handle Zeros for Missing Values

Previous_Amount = 
VAR CurrentMonth = MAX('MainTable'[Date])
RETURN 
    COALESCE(
        CALCULATE(
            SUM('MainTable'[Amount_Held]),
            FILTER(
                'MainTable',
                'MainTable'[ID] = EARLIER('MainTable'[ID]) &&
                'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
            )
        ),
        0
    )

Hope this helps.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Hi Sahir, Thanks so much for the response. This is helpful.

 

Luckily I was able to get this far and accomplish this. The issue that I am running into is related to the records that aren't in the latest month.

 

For example, Cust_1 is in 9-2024 but not in 10-2024. We would want to capture the fact that Cust_1 is now 0 in 10-2024. Is this possible?

 

I don't think your proposed solution would account for this but please correct me if I am wrong and thank you so much for your time 🙂

Hi @botaac, thank you for your feedback.

 

Can you please try this alternative:

 

1. Create a CustomerDateTable (to include all unique combinations of customers and Year-Months)

CustomerDateTable =
CROSSJOIN(
    DISTINCT('MainTable'[ID]),
    DISTINCT(DateTable[YearMonth])
)

2. Create a FinalTable (to ensure that if a customer was missing from a month, their amount for that month shows as zero)

FinalTable = 
ADDCOLUMNS(
    CustomerDateTable,
    "Date", MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date]),
    "Name", LOOKUPVALUE('MainTable'[Name], 'MainTable'[ID], CustomerDateTable[ID]),
    "Amount_Held", COALESCE(
        LOOKUPVALUE('MainTable'[Amount_Held], 'MainTable'[ID], CustomerDateTable[ID], 'MainTable'[Date], 
                    MAXX(FILTER('MainTable', 'MainTable'[Date] = EOMONTH(DateTable[Date], 0)), 'MainTable'[Date])
        ),
        0
    ),
    "Previous_Amount", 
        VAR CurrentMonth = MAXX(FILTER(DateTable, DateTable[YearMonth] = CustomerDateTable[YearMonth]), DateTable[Date])
        RETURN COALESCE(
            CALCULATE(
                SUM('MainTable'[Amount_Held]),
                FILTER(
                    'MainTable',
                    'MainTable'[ID] = CustomerDateTable[ID] &&
                    'MainTable'[Date] = EOMONTH(CurrentMonth, -1)
                )
            ),
            0
        )
)

Hope this helps 🙂


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors