Forum Discussion
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/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() |
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] )
6 Replies
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 () ) )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] )Can you please check below link, both sample data and output added
https://drive.google.com/drive/folders/1Fgnvl17hLfMKefH338isn3ukaHnmg_qU?usp=sharing
smpa01 Can you please check below link, both sample data and output added
https://drive.google.com/drive/folders/1Fgnvl17hLfMKefH338isn3ukaHnmg_qU?usp=sharing
