how to calculate running total if there is no records for remaining month

[VamshiGoud]

Hello Team,

I have below data where i need to calcluate running total. i have tried diffrent options , but values are repeating for the months which are not having values.

Ex :  Till july it's getting fine after july there is no data for the months, but value are repeating for rest of the months which i dont want . see output for ur better understanding.  Is there any function to restric if no values for months?

Input 

Year	Month	Values
2024	Apr	10
2024	May	20
2024	Jun	10
2024	Jul	5
2024	Aug
2024	Sep
2024	Oct
2024	Nov
2024	Dec
2024	Jan
2024	Feb
2024	Mar

Output

Year	Month	Values
2024	Apr	10
2024	May	30
2024	Jun	40
2024	Jul	45
2024	Aug	45
2024	Sep	45
2024	Oct	45
2024	Nov	45
2024	Dec	45
2024	Jan	45
2024	Feb	45
2024	Mar	45

Thanks 

[DataNinja777 · Accepted solution]

Hi @VamshiGoud ,

Using a proper calendar table is a best practice in Power BI for handling time-based calculations, including running totals. A calendar table ensures consistent date handling, proper filtering, and easier DAX calculations for tasks like time intelligence, comparisons, and aggregations.

You can write a calendar table by the following dax formula.

Calendar = ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)), -- Adjust date range as needed
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date])
)

Then create a relationship with your fact table by creating an M query added date column like below:

= Table.AddColumn(#"Changed Type1", "Custom Date", each Date.FromText(Text.PadStart(Text.From([Month]), 2, "0") & "/01/" & Text.From([Year])), type date)

Your data model will look like below:

Then you can write a running total dax measure on your fact table.

Running Total = 
CALCULATE(
    SUM('Table'[Values]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year] = MAX('Calendar'[Year]) &&
        'Calendar'[Month Number] <= MAX('Calendar'[Month Number])
    )
)

The resulting output will look like below:

I have attached an example pbix file for your reference.

Best regards,

[Anonymous]

Hi, @VamshiGoud 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bibiano_Geraldo]

Hi @VamshiGoud,

Add the bellow measure to stop comulative when value isBlank:

Measure = 
IF(
    ISBLANK(SUM('Table'[Values])), 
    BLANK(),
    [Your Comulative Measure]
)

Now you can use the above measure in your Matrix.

I hope this help you, if yes, please give a Kudo and accept as solution.

Thank you

[divyed · Accepted solution]

Hello @VamshiGoud ,

You can modify your dax to handle blank like below :

Running_Total_windows =

//Calculate running total and store in variable

VAR _Res =CALCULATE (

    SUM ( Tab_2[Value] ),

    WINDOW (

        1,

        ABS,

        0,

        REL,

        SUMMARIZE(Tab_2,Tab_2[Name],Tab_2[Value]),

        ORDERBY ( Tab_2[Name])

    )

)

//Return where no blank

RETURN IF(ISBLANK(Tab_2[Value]),BLANK(),_Res)

I hope this helps . Mark this as solution if this helps or give a thumbs up.

Cheers

[VamshiGoud]

@divyed 

Thanks for ur reply. but why cumulative values are repeating from Aug to dec when there is no value. could u pls check

[divyed]

Hello @VamshiGoud ,

It should not, please check if dax is correct and month are orderes properly.

You can paste youe dax as well to chekc further.

Cheers.

[DataNinja777 · Accepted solution]

Hi @VamshiGoud ,

Using a proper calendar table is a best practice in Power BI for handling time-based calculations, including running totals. A calendar table ensures consistent date handling, proper filtering, and easier DAX calculations for tasks like time intelligence, comparisons, and aggregations.

You can write a calendar table by the following dax formula.

Calendar = ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)), -- Adjust date range as needed
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date])
)

Then create a relationship with your fact table by creating an M query added date column like below:

= Table.AddColumn(#"Changed Type1", "Custom Date", each Date.FromText(Text.PadStart(Text.From([Month]), 2, "0") & "/01/" & Text.From([Year])), type date)

Your data model will look like below:

Then you can write a running total dax measure on your fact table.

Running Total = 
CALCULATE(
    SUM('Table'[Values]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Year] = MAX('Calendar'[Year]) &&
        'Calendar'[Month Number] <= MAX('Calendar'[Month Number])
    )
)

The resulting output will look like below:

I have attached an example pbix file for your reference.

Best regards,

[uzuntasgokberk]

Hello @VamshiGoud ,
Add a new colum which gives only date format.
And after could you please use this measure:
CALCULATE( SUM(Values),FILTER(ALL(DimDate[Date]),DimDate[Date) <=MAX(DimDate[Date])))

Kind Regards,
Gökberk Uzuntaş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

[SamWiseOwl]

Hi @VamshiGoud 

You could change your measure to return a 0 or blank instead.

Hide extra =

if( SUM(table[Values]) = blank(), blank(), [Your measure])

If there is no value then don't show the running total.