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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

DAX to get a Running Total From a Referenced Measure

I have two columns. One displays actual data throughout the year (up through Feb 2022 so far) and the other has projections for the whole year.

 

I have a combined column that via a measure has the actual data for the months of January and February of 2022, then with the projections for March-December of 2022.

 

For some reason I cannot successfully make a third column that has a cumulative total where the first two months are of actual data and the remaining are projections. Any help is appreciated... Thanks!

5 REPLIES 5
Nathaniel_C
Super User
Super User

Hi @Anonymous 
Please try this and note that I used a time period rather than a date, but a date can be substituted in for that:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

 

Running Total = 
VAR _currentPeriod =
    MAX ( RTotal[Period] )
VAR _calc =
    CALCULATE (
        SUM ( RTotal[Actuals]) + Sum(RTotal[Projections]),
        RTotal[Period]  <= _currentPeriod,
        ALL ( RTotal ))
    
RETURN
    _calc

 

Nathaniel_C_0-1653439400717.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

I think that is heading in the right direction but I want to clarify that the column of projections does have values for every time period, not just for the time periods I will use it for. Thank you. @Nathaniel_C 

Hi @Anonymous  so if there is an actual, use that instead of the projection? I can show you that.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C Correct. 

I have actual data for the months leading up to the current month, and projections for every month of the year, including months prior.

What I would like to do is take a cumulative total after every month using the actual data for months that have happened and adding the projections for future months afterwards.

Hi @Anonymous ,
Add a conditional column in Power Query - see picture - Basically if Actuals is not blank or zero, then use Actuals, else use Projections.
Please let me know if you have any questions, and please mark this as a solution so that others may find it more easily.

Thank you,

Nathaniel

Nathaniel_C_0-1653507399356.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.