Forum Discussion
Creating Index base 100 charts
Hi again hcova7
Thanks for testing that out, and for highlighting the issue with blanks 🙂
I have attached an updated workbook.
To fill the blanks, we can make these updates:
Close Latest := VAR MaxDate = MAX ( 'Calendar'[Date] ) VAR MaxDateWithData = CALCULATE ( MAX ( Table1[DATE] ), 'Calendar'[Date] <= MaxDate ) VAR Result = CALCULATE ( [Close Average], 'Calendar'[Date] = MaxDateWithData ) RETURN ResultIndexBase100 Fix := VAR MinDateFiltered = CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) ) VAR MinDateInPeriod = CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED ( 'Calendar' ) ) VAR MaxDateToStartOfPeriod = CALCULATE ( MAX ( Table1[Date] ), 'Calendar'[Date] <= MinDateFiltered ) VAR _BaseDate = COALESCE ( MaxDateToStartOfPeriod, MinDateInPeriod ) VAR _BasePrice = CALCULATE ( [Close Average], 'Calendar'[Date] = _BaseDate ) VAR _Index100 = DIVIDE ( [Close Latest], _BasePrice ) * 100 RETURN _Index100I did include some alternative formulations of the Index measure for comparison:
- IndexBase100 Fix v2 (performs about the same)
- IndexBase100 Fix v3 (quite a bit slower)
Is this what you were looking for and is performance acceptable?
Dear Owen.
Thanks a lot for your time and your great answer. It works in 95% for my job.
However I have a problem when I try to include an index that publishes data values for some dates only and when I try to chart it with major indexes like DJI, NASDAQ, etc, that contain data values in all dates.
Let me explain this with a real case that I have to deal with (I have changed the real fund name)
Royal Cloud is private fund that publish their data some dates during the month. No everyday.
When I try to compare it with the the major indexes here the problem:
As you can see in the above Excel table, Royal Cloud Index base 100 is in column D and calculated according your DAX measure. This measure leaves some cells in blank so the chart for this fund displays a scatter plot (see right chart).
However, if the measure could fill the gaps with the its last calculated value, the plot will be a continuos line (see below picture). I have manually done this. I have filled the gaps in column D writing in red the last calculated values from the measure.
Question:
How can I change the DAX measure so the blank cells shown in the Index base 100 table can be filled with the last value calculated by the measure?
Best regards
Here the link for the worksheet with the Royal Cloud Fund data:
Hi again hcova7
Thanks for testing that out, and for highlighting the issue with blanks 🙂
I have attached an updated workbook.
To fill the blanks, we can make these updates:
Close Latest :=
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR MaxDateWithData =
CALCULATE ( MAX ( Table1[DATE] ), 'Calendar'[Date] <= MaxDate )
VAR Result =
CALCULATE ( [Close Average], 'Calendar'[Date] = MaxDateWithData )
RETURN
ResultIndexBase100 Fix :=
VAR MinDateFiltered =
CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR MinDateInPeriod =
CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR MaxDateToStartOfPeriod =
CALCULATE ( MAX ( Table1[Date] ), 'Calendar'[Date] <= MinDateFiltered )
VAR _BaseDate =
COALESCE ( MaxDateToStartOfPeriod, MinDateInPeriod )
VAR _BasePrice =
CALCULATE ( [Close Average], 'Calendar'[Date] = _BaseDate )
VAR _Index100 =
DIVIDE ( [Close Latest], _BasePrice ) * 100
RETURN
_Index100
I did include some alternative formulations of the Index measure for comparison:
- IndexBase100 Fix v2 (performs about the same)
- IndexBase100 Fix v3 (quite a bit slower)
Is this what you were looking for and is performance acceptable?