Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

can we use IF condition while using TOTALYTD function in calculated measure in POWER BI Desktop...

Hello, I am trying various formulas but didn't work any of them..

 

I think IF CONDITION IS NOT WORKING in a measure...

 

Comparison -YTD = CALCULATE(TOTALYTD(SUM(FACT_V1[V1_AMOUNT_EUR])/1000,'01_PERIOD'[PERIOD]),FILTER(FACT_V1_BU, FACT_V1_BU[MONTH]=FACT_V1_BU[MONTH_V1]))

 

Comparison -YTD1= SUMX(FACT_V1_BU, IF(FACT_V1_BU[MONTH]=FACT_V1_BU[MONTH_V1],TOTALYTD(SUM(FACT_V1_BU[V1_AMOUNT_EUR])/1000,'01_PERIOD'[PERIOD])))

 

 

Above Results are showing MTD Values only...

 

Am I missing something.. 

  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi Anonymous ,

     

    IF CONDITION can work in a measure, such as:

    Measure 4 = 
IF(
    MAX('Table 2'[Date]) <= DATE(2020, 12, 1),
    TOTALYTD(
        SUM('Table 2'[Column]),
        'Table 2'[Date]
    )
)

    For your Problem, maybe you can try this:

    Comparison -YTD1 = 
 SUMX(
     FILTER(
         FACT_V1_BU,
         FACT_V1_BU[MONTH]=FACT_V1_BU[MONTH_V1]
     ),
     TOTALYTD(SUM(FACT_V1_BU[V1_AMOUNT_EUR])/1000,'01_PERIOD'[PERIOD])
 )

    If the formula can't help you, please give me a sample data.

     

    Best regards,
    Lionel Chen

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

     

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous what you are trying to achieve, share sample data, business problem and expected output.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    IF CONDITION can work in a measure, such as:

    Measure 4 = 
IF(
    MAX('Table 2'[Date]) <= DATE(2020, 12, 1),
    TOTALYTD(
        SUM('Table 2'[Column]),
        'Table 2'[Date]
    )
)

    For your Problem, maybe you can try this:

    Comparison -YTD1 = 
 SUMX(
     FILTER(
         FACT_V1_BU,
         FACT_V1_BU[MONTH]=FACT_V1_BU[MONTH_V1]
     ),
     TOTALYTD(SUM(FACT_V1_BU[V1_AMOUNT_EUR])/1000,'01_PERIOD'[PERIOD])
 )

    If the formula can't help you, please give me a sample data.

     

    Best regards,
    Lionel Chen

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