Only calculate when data is available - budget vs turnover - Dax statements

[aletho]

Good morning

I'm still pretty new at writing DAX statements, but "beginning dax with power bi" book is on it way!

In the meantime, I hope that one of you could help me with two calculations regarding budgeting. 

First,

The column "oms vs budget %" is calculated as follow:  Oms vs Budget % = ([Oms. FBP]-SUM('BoligPortal OMS'[Budget - FBP]))/[Oms. FBP]

I would like it only to calculate it when there is a number in the "budget - FBP" and the "Oms. FBP" column, so I do not get either "-infinity" or "100%" return.

Secondly, I would like to make a calculation "budget - FBP" - "Oms. FBP", but only for the months past in this year. So it would be Jan trough Aug, and next month it would include September and so forth. Giving me the insight in if the turnover (Oms. FBP) is doing in regards to the budget.

All help appreciated  

[v-shex-msft]

HI @aletho,

#1. In my opinion, if statement will suitable for your requirement. You can add it with conditions to check columns value which you mentioned:

Oms vs Budget % =
IF (
    [Oms. FBP] <> BLANK ()
        && [Budget - FBP] <> BLANK (),
    ( [Oms. FBP] - SUM ( 'BoligPortal OMS'[Budget - FBP] ) )
        / [Oms. FBP]
)

#2. For this scenario, you can use DATE and TODAY functions to create a virtual calendar in formula and use 'in' keyword to check if current date is in range:

Measure =
VAR currDate =
    MAX ( Date[Date] )
RETURN
    IF (
        currDate
            IN CALENDAR (
                DATE ( YEAR ( TODAY () ), 1, 1 ),
                DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 )
                    - 1
            ),
        SUM ( 'BoligPortal OMS'[budget - FBP] ) - SUM ( 'BoligPortal OMS'[Oms. FBP] )
    )

Regards,

Xiaoxin Sheng

[aletho]

Hello Xiaoxin Sheng

The first one works like a charm but the second one gives me some trouble. 

This is the message I get return is that the syntax for date is incorrect.

I don't seem to have the VAR or currdate functions? 

Here is the link to my data in power bi: 

https://drive.google.com/file/d/1_8KVKtRawFczXngFN_xeOr_KW_b7Zg3y/view?usp=sharing

and the column and the measure I would like calculated: 

[Oms. FBP]-SUM('BoligPortal OMS'[Budget - FBP]

Thank you for helping

[v-shex-msft · Accepted solution]

Hi @aletho,

You can try to use below formula, it seems like 'date' table name has conflict with DATE function:

Budget vs oms. FBP this year = 
VAR currDate =
    MAX ( 'Date'[Date])
RETURN
    IF (
        currDate
            IN CALENDAR (
                DATE ( YEAR ( TODAY () ), 1, 1 ),
                DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 )
                    - 1
            ),
        SUM ( 'BoligPortal OMS'[budget - FBP] ) -  'PaymentLog'[Oms. FBP] 
    )

Regards,

Xiaoxin Sheng