Forum Discussion

tfr111's avatar
tfr111
Frequent Visitor
1 year ago
Solved

YTD and SUMX issue

Hi,   I'm trying to calculate the running total using the DATESYTD function. It works well on raw data (line 1+2). If I rebase the data (deducting an offset; line 3+4) I can't get the right result....
Need Help
  • Power2G's avatar
    Power2G
    1 year ago

    Thanks for the feedback tfr111 

    Instead of applying SUMX on Financials, you should iterate over a pre-filtered table that ensures only the relevant rows are summed up in a running total.

     

    In-year impact rebased =
VAR RebasedTable =
    ADDCOLUMNS(
        SUMMARIZE(Financials, 'Financials'[Date]),
        "RebasedImpact", DIVIDE([Annualized impact rebased], 12)
    )
RETURN
    CALCULATE(
        SUMX(RebasedTable, [RebasedImpact]),
        DATESYTD('Financials'[Date].[Date])
    )

    test via this also:

    EVALUATE ADDCOLUMNS(
    SUMMARIZE(Financials, 'Financials'[Date]),
    "RebasedImpact", DIVIDE([Annualized impact rebased], 12)
)
tfr111's avatar
tfr111
Frequent Visitor
1 year ago

Hi Power2G,

 

thank you very much for your answer.

I adapted In-year impact rebased as suggested, but it didn't change the result.

 

I also created a table to see the results, but I'm not sure how to interpret the results.

  • Annualized impact: looks good: in the data set value only exist for the first of each month
  • In-year impact: looks good; the fact that numbers are only present for each day of the month does not seem to make a difference. The total is correct
  • Annualized impact rebased: looks good: the negative numbers are a result of deducting the offset of 275 vs an empty cell in annualized impact. The total is correct
  • In-year impact : Individual numbers as well as the total are wrong. The calculation seems to somehow lose the context of Annualized impact rebased.

 

I appreciate any additional ideas.

Best regards

Power2G's avatar
Power2G
Frequent Visitor
1 year ago

Thanks for the feedback tfr111 

Instead of applying SUMX on Financials, you should iterate over a pre-filtered table that ensures only the relevant rows are summed up in a running total.

 

In-year impact rebased =
VAR RebasedTable =
    ADDCOLUMNS(
        SUMMARIZE(Financials, 'Financials'[Date]),
        "RebasedImpact", DIVIDE([Annualized impact rebased], 12)
    )
RETURN
    CALCULATE(
        SUMX(RebasedTable, [RebasedImpact]),
        DATESYTD('Financials'[Date].[Date])
    )

test via this also:

EVALUATE ADDCOLUMNS(
    SUMMARIZE(Financials, 'Financials'[Date]),
    "RebasedImpact", DIVIDE([Annualized impact rebased], 12)
)