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]
    )

smpa01's avatar
smpa01
Community Champion
4 years ago

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 ()
        )
    )
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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]
    )
      • 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]
    )