Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX formula help

Please help I want to calculate opening balance based on released date and completion date For any date, i want to calculaten opening balance based on below conditions: https://drive.google.com/dr...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Try this as a measure on a table with CALENDAR[Date]:

    Opening Balance = 
VAR ReportDate = SELECTEDVALUE ( 'CALENDAR'[Date] )
RETURN
    SUMX (
        FILTER (
            CALCULATETABLE ( DATA, REMOVEFILTERS ( 'CALENDAR' ) ),
            ( DATA[RELEASED DATE] < ReportDate && NOT ( ISBLANK ( DATA[RELEASED DATE] ) ) ) &&
            ( DATA[COMPLETION DATE] >= ReportDate || ISBLANK ( DATA[COMPLETION DATE] ) )
        ),
        DATA[QUANTITY]
    )

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

Try this as a measure on a table with CALENDAR[Date]:

Opening Balance = 
VAR ReportDate = SELECTEDVALUE ( 'CALENDAR'[Date] )
RETURN
    SUMX (
        FILTER (
            CALCULATETABLE ( DATA, REMOVEFILTERS ( 'CALENDAR' ) ),
            ( DATA[RELEASED DATE] < ReportDate && NOT ( ISBLANK ( DATA[RELEASED DATE] ) ) ) &&
            ( DATA[COMPLETION DATE] >= ReportDate || ISBLANK ( DATA[COMPLETION DATE] ) )
        ),
        DATA[QUANTITY]
    )

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks AlexisOlson for the wonderful solution.