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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks AlexisOlson for the wonderful solution.