Forum Discussion

Dannismen's avatar
Dannismen
Icon for Helper I rankHelper I
5 years ago
Solved

Cumulative/YTD formulas wrong

Hi everyone.    I've used this formula to calculate ytd value but somehow at March 28th, 2021 it always gave me a spike it shouldn't have.    Can someone plz help me on the formula?   https://d...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    FYTD = 
VAR __to = MAX( Combined[fiscal_BOW] )
VAR __from =
    MINX(
        CALCULATETABLE(
            Combined,
            ALL( Combined[fiscal_BOW] ),
            Combined[fiscal_BOW] <= __to
        ),
        Combined[fiscal_BOW]
    )
RETURN
    CALCULATE(
        SUM( Combined[Metric_Value] ),
        DATESBETWEEN( Combined[fiscal_BOW], __from, __to )
    )

Dannismen's avatar
Dannismen
Icon for Helper I rankHelper I
5 years ago
Formula:
 
Cumulative Value =
CALCULATE( sum(Combined[Metric_Value]),
FILTER( Combined,
Combined[Fiscal_BOW] <= EARLIER(Combined[Fiscal_BOW])
&& Combined[fiscal_year] = EARLIER(Combined[fiscal_year])
&& Combined[Metric_Name] = EARLIER(Combined[Metric_Name])
&& Combined[standardized_name] = EARLIER(Combined[standardized_name])
&& Combined[DPR_Entity] = EARLIER(Combined[DPR_Entity])))