Forum Discussion

vjnvinod's avatar
vjnvinod
Icon for Impactful Individual rankImpactful Individual
6 years ago
Solved

YTD plan measure

Hi All

 

I need help to  create a measure for YTD Plan from the Total plan coloumn(see below screenshot)

where my condition should be if any of my sum(actuals) is blank for myAccounting period, then addup the plan numbers

which will be my YTD Plan number

YTD planned number= if sum(b. Actuals USD Current) is blank  for accounting period then

SUM(DataDumpFull[Plan Total])
 
 
 
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    vjnvinod 
    For other columns, you need to make a little change to the formula. It is categorized using the ALLEXCEPT function(Bold), you can always change the expression column. For example, I used it on Child Cost Type. 

     

    YTD measure =
    CALCULATE(
        SUM(DataDumpFull[Plan Total]),
            FILTER(ALLEXCEPT(DataDumpFull, DataDumpFull[Child Cost Type]),
                CALCULATE(SUM([b. Actuals USD Current]),
                    ALLEXCEPT(DataDumpFull, DataDumpFull['Raw Data'[3. Accounting Period]]])) <> BLANK()))
     

    I am not sure whether it works for all, becasue you have too many columns with different data types, you can alway give it a try. 

    Best,
    Paul

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    vjnvinod 
    For other columns, you need to make a little change to the formula. It is categorized using the ALLEXCEPT function(Bold), you can always change the expression column. For example, I used it on Child Cost Type. 

     

    YTD measure =
    CALCULATE(
        SUM(DataDumpFull[Plan Total]),
            FILTER(ALLEXCEPT(DataDumpFull, DataDumpFull[Child Cost Type]),
                CALCULATE(SUM([b. Actuals USD Current]),
                    ALLEXCEPT(DataDumpFull, DataDumpFull['Raw Data'[3. Accounting Period]]])) <> BLANK()))
     

    I am not sure whether it works for all, becasue you have too many columns with different data types, you can alway give it a try. 

    Best,
    Paul

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, vjnvinod 

    If I understand you correctly, this formula should work.

     

    YTD Measure = 
CALCULATE (
    SUM ( Sheet1[Plan Total] ), 
    FILTER (
        ALL ( Sheet1 ),
        COUNTROWS(
            FILTER ( Sheet1, EARLIER ( Sheet1[ Period] ) <= Sheet1[ Period] )
        )
    ), FILTER (ALL(Sheet1),[Actual] <> BLANK ()))



    Best,
    Paul

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.