DAX formula help

[Anonymous]

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/drive/folders/1Fgnvl17hLfMKefH338isn3ukaHnmg_qU?usp=sharing 

OPENING BAL = RELEASED DATE <  REPORT DATE

AND  RELEASED DATE <> BLANK()

AND

COMPLETION DATE >= REPORT DATE

OR COMPLETION DATE = BLANK()

[smpa01]

@Anonymous  try this

measure =
CALCULATE (
    SUM ( tbl[Quantity] ),
    FILTER ( tbl, tbl[ReleaseDate] < TODAY () || tbl[ReleaseDate] <> BLANK () )
)
    + CALCULATE (
        SUM ( tbl[Quantity] ),
        FILTER (
            tbl,
            tbl[CompletionDate] > TODAY ()
                || tbl[CompletionDate] <> BLANK ()
        )
    )

[Anonymous]

@smpa01 Can you please check below link, both sample data and output added

https://drive.google.com/drive/folders/1Fgnvl17hLfMKefH338isn3ukaHnmg_qU?usp=sharing 

[AlexisOlson]

@smpa01 I think your first || should be &&, your last <> should be =, and I don't think you want to separate this into two sums.

@Anonymous I think this might be a bit closer:

Opening Balance = 
VAR ReportDate = TODAY () /* Or however this is determined*/
RETURN
    SUMX (
        FILTER (
            Table1,
            ( Table1[ReleaseDate] < ReportDate && NOT ( ISBLANK ( Table1[ReleaseDate] ) ) ) &&
            ( Table1[CompletionDate] >= ReportDate || ISBLANK ( Table1[CompletionDate] ) )
        ),
        Table1[Quantity]
    )

[Anonymous]

@AlexisOlson 

Can you please check below link, both sample data and output added

https://drive.google.com/drive/folders/1Fgnvl17hLfMKefH338isn3ukaHnmg_qU?usp=sharing 

[AlexisOlson · Accepted solution]

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]

Thanks @AlexisOlson for the wonderful solution.