I am creating a report on power bi with last 20 days of option trading daily reports of a commodity. it has one table with 6 columns : DATE, MONTH, STRIKE, P/C, DELTA, OI. the DATE column has all the trading dates, the MONTH column has the contract month option, the STRIKE column has all the strike prices that were traded on each day for each contract month, the P/C has P or C which means puts or calls, the DELTA has the delta and OI has the oi or option interest. I created some measures to calculate the total OI, OI for last day, OI change. the measures are as follows: Total OI = SUM(Table1[OI]) OI previous day = VAR CurrentDate = SELECTEDVALUE(Table1[DATE]) VAR LastWorkingDay= CALCULATE( MAX(Table1[DATE]), FILTER( ALL(Table1), Table1[DATE]<CurrentDate ) ) RETURN IF( NOT(ISBLANK(LastWorkingDay)), CALCULATE([Total OI],Table1[DATE]=LastWorkingDay), BLANK() ) OI change = [Total OI]-[OI previous day] Now when i create a column chart or clustered column chart with DATE on x-axis and OI change on y-axix the OI change value is showing the value of Total OI for each date. i added Total OI and OI change on the y-axis and both the bars for each date is showing the same value. but its not the actual value for OI change. why is this happening? how can i make this chart with the bars showing accurate value for OI change?
i created a table to see the value shown by my measures are correct with STRIKE, Total OI, OI previous day, OI change as columns and add a slicer with DATE it shows the correct value on the table for each strike for the selected date on the slicer. but as soon as i remove the slicer it shows incorrect values. it shows the value of Total OI in OI change column too. i understand it cant show change in OI without any DATE on the table since its relative but why would it show the value of Total OI. and why it cant show the actual OI change value in a clustered column chart against DATE? there it has the relative DATE to calculate the OI change for each date from previous one.
please help its urgent i need to make a chart to show two bars side by side with values of Total OI and OI change for each date.
