how to create a measure to return value for previous nonblank date
im trying to create a measure that returns the closing price for the previous day for a stock. however in some days the stock market is closed so the previous date in this case will be blank. i used the following measure to get the value for last non blank date but its only working in table visual when the date field is added previous day price1 = CALCULATE(SUM('stocks data'[Close]), OFFSET(-1,ALLSELECTED('stocks data'[Date]),ORDERBY('stocks data'[Date],ASC))) when i added the measure to card visual it's returing the sum value of the previous closing price instead of the previous day. in the selected date period , the previous nonblank date is 12-9-2022 and the measure should return142.16 and the day on day change value should be 1.61% any idea on how to fix the measure?
Get accumulated pending tasks
Hi. I have the following example table: id opening date closing date 1 01/01/2021 01/02/2021 2 01/01/2021 01/02/2021 3 01/01/2021 01/02/2021 4 01/01/2021 01/02/2021 5 01/01/2021 6 01/01/2021 7 01/01/2021 8 01/01/2021 9 01/02/2021 10 01/02/2021 11 01/02/2021 12 01/02/2021 13 01/02/2021 14 01/02/2021 15 01/02/2021 16 01/02/2021 17 01/03/2021 18 01/03/2021 19 01/03/2021 20 01/03/2021 Each row of the table is a task (demand) that has been generated for someone. The objective is to create a histogram that shows the amount of accumulated open demands. An open demand is one that does not have an closing date, or whose closing date is later than the month bar in the histogram. The result should be something like: Note that 8 demands were generated in January, 8 in February and 4 in March, but 4 were closed in February. I was helped before and came up to this solution, but it only works for 2021 dates. If I use 2022 data, the calculation doesn't work: Measure = VAR _filter = FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] <= MAX ( 'Calendar'[Year] ) && 'Calendar'[Month] <= MAX ( 'Calendar'[Month] ) ) VAR _opening = CALCULATE ( COUNT ( demand[opening date] ), TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] ) ) VAR _closing = CALCULATE ( COUNT ( demand[closing date] ), TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] ) ) VAR _accumulatedOpening = CALCULATE ( CALCULATE ( COUNT ( demand[opening date] ), TREATAS ( VALUES ( 'Calendar'[Date] ), demand[opening date] ) ), _filter, demand ) VAR _accumulatedClosing = CALCULATE ( CALCULATE ( COUNT ( demand[closing date] ), TREATAS ( VALUES ( 'Calendar'[Date] ), demand[closing date] ) ), _filter, demand ) VAR _diff = _accumulatedOpening - _accumulatedClosing RETURN IF ( _opening <> BLANK () || _closing <> BLANK (), _diff )
