Forum Discussion

Nikki's avatar
Nikki
Icon for Helper II rankHelper II
9 years ago
Solved

cumulative running total formula that doesnt continue to display past the values entered

  i have to show actuals, budget, forecast on a line/bar chart. the issue is that values for "actuals" stop in june but continue to show the same value till december. see pic. i created the running ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Nikki,

    I note that you have renamed ProjectActuals measure to Project Actuals.

    Right-Click your table and select "New measure", then enter one of following formula and check the result.

    ActualRunning = CALCULATE(Financials[Project Actuals],FILTER(ALLSELECTED(Financials[Month]),Financials[Month]<=MAX(Financials[Month])&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

    ActualRunning = CALCULATE(Financials[Project Actuals],FILTER(ALLSELECTED(Financials[Month]), ISNOORAFTER(Financials[Month],MAX(Financials[Month]),DESC)&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))


    If the issue still occurs, please share sample data of your table or share me the PBIX file.

    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Nikki,

You mean that [ProjectActuals] is a measure, right? If so, what is the result when you use one of the following formulas to create your measure?

ActualRunning = CALCULATE(Financials[ProjectActuals],FILTER(ALLSELECTED(Financials[Month]),Financials[Month]<=MAX(Financials[Month])&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

ActualRunning = CALCULATE(Financials[ProjectActuals],FILTER(ALLSELECTED(Financials[Month]), ISNOORAFTER(Financials[Month],MAX(Financials[Month]),DESC)&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

Thanks,
Lydia Zhang

Nikki's avatar
Nikki
Icon for Helper II rankHelper II
9 years ago

yes it is a measure you are right. i created it from the quick measures. below is a screenshot. hope this helps - thanks heaps

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Nikki,

    I note that you have renamed ProjectActuals measure to Project Actuals.

    Right-Click your table and select "New measure", then enter one of following formula and check the result.

    ActualRunning = CALCULATE(Financials[Project Actuals],FILTER(ALLSELECTED(Financials[Month]),Financials[Month]<=MAX(Financials[Month])&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))

    ActualRunning = CALCULATE(Financials[Project Actuals],FILTER(ALLSELECTED(Financials[Month]), ISNOORAFTER(Financials[Month],MAX(Financials[Month]),DESC)&& MONTH(MAX(Financials[Month]))<=MONTH(TODAY())+1))


    If the issue still occurs, please share sample data of your table or share me the PBIX file.

    Thanks,
    Lydia Zhang

  • Nikki's avatar
    Nikki
    Icon for Helper II rankHelper II
    9 years ago

    also this is the formula for project actuals ( the measure)

    Project Actuals = [DateValue for Project Costs for Actuals - Capex] + [DateValue for Project Costs for Actuals - Opex]

     

    and below are formulas for both above too - also measures :)

     

    DateValue for Project Costs for Actuals - Capex =
    CALCULATE(
     'Financials'[DateValue for Project Costs],
     TREATAS({("Actuals - Capex")}, 'Financials'[Title])
    )

     

    DateValue for Project Costs for Actuals - Opex =
    CALCULATE(
     'Financials'[DateValue for Project Costs],
     TREATAS({("Actuals - Opex")}, 'Financials'[Title])
    )