Forum Discussion

Dannismen's avatar
Dannismen
Helper 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://drive.google.com/file/d/1ttQDeOPGBzaigUO3b-FGcH4Dio4HuDiY/view?usp=sharing

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 )
    )

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    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
    Helper 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])))