Show Variance% only till both column values are present

[Anonymous]

Hello,

I Have below chart

Sample Data:

I am struggling to correct below DAX to display chart as shown below.

I would like to show variance% only  IF(((YearCount=2)&&(NOT(ISBLANK(SecondSelectedValue))), DIVIDE(SecondSelectedValue - FirstSelectedValue, FirstSelectedValue )) as I have 2020 data only till today(2020-05-W2)

I need to display Variance% as shown in the below chart.

Please let me know how to correct above DAX to display Variance% only till current week

Thank you!

[v-lili6-msft]

HI  @Anonymous 

Add a conditional in the formula as below:

    IF (
        (
            ( YearCount = 2 )
                && ( NOT ( ISBLANK ( SecondSelectedValue*FirstSelectedValue ) ) )
        ),
        DIVIDE ( SecondSelectedValue - FirstSelectedValue, FirstSelectedValue )
    )

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

Thank you for your reply!

I have updated the formula as per your suggestion. It didn't work.

Bill Variance % =
VAR YearCount= CALCULATE(COUNTROWS(FILTERS('Weekly WAMR vs Pumpage'[Year])),ALLSELECTED('Weekly WAMR vs Pumpage'[Year]))
VAR Top1N =
    VALUE (
        CALCULATE (
            CONCATENATEX ( TOPN ( 1, VALUES ( 'Weekly WAMR vs Pumpage'[Year] ) ), 'Weekly WAMR vs Pumpage'[Year], ", " )
        )
    )
VAR Top2N =
    VALUE (
        CALCULATE (
            CONCATENATEX (
                TOPN ( 1, VALUES ( 'Weekly WAMR vs Pumpage'[Year] ), 'Weekly WAMR vs Pumpage'[Year], DESC ),
                'Weekly WAMR vs Pumpage'[Year],
                ", "
            )
        )
    )
VAR FirstSelectedValue =
    CALCULATE ( SUM ( 'Weekly WAMR vs Pumpage'[AVG_BILL_CONS_MGD] ), 'Weekly WAMR vs Pumpage'[Year] = Top1N )
VAR SecondSelectedValue =
    CALCULATE ( SUM ( 'Weekly WAMR vs Pumpage'[AVG_BILL_CONS_MGD] ), 'Weekly WAMR vs Pumpage'[Year] = Top2N )
RETURN IF(
          (
           (YearCount=2)
              &&(NOT(ISBLANK(SecondSelectedValue*FirstSelectedValue)))
           ),
           DIVIDE(SecondSelectedValue - FirstSelectedValue, FirstSelectedValue )
       )

Still Variance% line is showing in the chart for upcoming weeks as 0%.

[v-lili6-msft]

hi  @Anonymous 

If possible, could you please share your sample pbix file for us have a test, that will be a great help.

 Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

I do not see any option to attach .pbix file or excel file here.

[Anonymous]

Please let me know how to attach .pbix file and excel file here.

Thank you!

Regards,

Aswini

[v-lili6-msft]

hi @Anonymous 

You can upload it to OneDrive for business and post the link here. Do mask sensitive data before uploading.

Regards,

Lin