Forum Discussion

Jenn_Hill_13's avatar
Jenn_Hill_13
New Member
6 years ago

If statement for days with no data

Hello, 

 

I am trying to create a DAX mesaure that will show the accumnulated total of a balance sheet line item using th GL data. I have created the following measure that pulls the accumulated balance for the last transaction for that period using the MAX index. 

 

The measure I have is as follows: 

BalanceCheck = CALCULATE(
SUM('tGL Combined'[Balance]),
FILTER('tGL Combined','tGL Combined'[Index]=max('tGL Combined'[Index]))
)
The problem I am having is that the visual looks choppy because the GL data has missing days where there was no transactional data. I want to add an IF Statement that if there is a day with no transactional data it will pull the accumulated balance from the previous day. 
 
Can someone please help me add to my exisitng measure?
 
Thank!
 
 

1 Reply

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Jenn_Hill_13 
    Try this: did it free hand, so let me know if you have any issues. Using the Index to go back 1 row.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel


    BalanceCheck =

VAR _presIndex = 'tGL Combined'[Index]

VAR _prevIndex = 'tGL Combined'[Index] - 1

VAR _presCalc =
    CALCULATE (
        SUM ( 'tGL Combined'[Balance] ),
        FILTER ( 'tGL Combined', 'tGL Combined'[Index] = MAX ( 'tGL Combined'[Index] ) )
    )
VAR _prevCalc =
    CALCULATE (
        SUM ( 'tGL Combined'[Balance] ),
        FILTER ( 'tGL Combined', 'tGL Combined'[Index] = prevIndex )
    )
RETURN
    IF ( _presCalc = 0, _prevCalc, presCalc )