Forum Discussion
Dynamic deltas based on date slicer
Hi,
I have a problem in Power BI that I can’t seem to resolve and maybe one of you can.
In my report I have a graph showing Delta inception-to-date of a mutual fund. I am trying to set it up so that I can use a slicer to change which date it calculates the delta from, starting at 0%. Does anyone have any ideas of how to solve this?
Another challenge is that there are several classes for one fund, and thus different NAV (Net Asset Value) are registered on the same date. I am quite new to Power BI and would appreciate any help and recommendations solving this.
Sample data:
| AAL | kr 1 521,01 | 30.04.2020 | 10,73 % | 52,10 % | 25.11.2016 | Class A |
| AAL | kr 1 522,54 | 31.05.2020 | 0,10 % | 52,25 % | 25.11.2016 | Class A |
| AAL | kr 1 519,80 | 30.06.2020 | -0,18 % | 51,98 % | 25.11.2016 | Class A |
| AAL | kr 1 421,79 | 30.04.2020 | 10,80 % | 42,18 % | 31.03.2017 | Class B |
| AAL | kr 1 422,44 | 30.06.2020 | -0,11 % | 42,24 % | 31.03.2017 | Class B |
| AAL | kr 1 424,07 | 31.05.2020 | 0,16 % | 42,41 % | 31.03.2017 | Class B |
| AAL | kr 1 395,29 | 30.06.2020 | 0,45 % | 39,53 % | 29.06.2016 | Class H |
| AAL | kr 1 322,66 | 30.04.2020 | 12,23 % | 32,27 % | 29.06.2016 | Class H |
| AAL | kr 1 389,01 | 31.05.2020 | 5,02 % | 38,90 % | 29.06.2016 | Class H |
| AAL | kr 1 685,34 | 31.05.2020 | 0,18 % | 68,53 % | 25.05.2016 | Class I |
| AAL | kr 1 682,31 | 30.04.2020 | 10,82 % | 68,23 % | 25.05.2016 | Class I |
| AAL | kr 1 683,78 | 30.06.2020 | -0,09 % | 68,38 % | 25.05.2016 | Class I |
Hi olasmith ,
How about this? Calculate by day.
Measure = VAR StartDate = MINX ( ALLSELECTED ( Dates ), Dates[Date] ) VAR EndDate = MAXX ( ALLSELECTED ( Dates ), Dates[Date] ) VAR CurrentDate = MAX ( Dates[Date] ) VAR StartDateValue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] = StartDate ) VAR CurrentDateValue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] = CurrentDate ) RETURN IF ( MAX ( Dates[Date] ) >= StartDate && MAX ( Dates[Date] ) <= EndDate, DIVIDE ( CurrentDateValue - StartDateValue, StartDateValue ) )Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Icey ,
Yes, I'll try to explain it better.
So in the image above you can se one fund's return inception to date compared to an index. What I am trying to achieve is that when using the slicer, the visual shows the return from the selected start date up to this date. So let's say that you want to see the return from 31st October 2016 to date, then it starts at 0% in October and calculate the percentage change of NAV (Net Asset Value).
In the visual above, I have already used another slicer to select which index, and which fund class I want to see.
Hope this helps,Ola
Hi olasmith ,
How about creating another seperate Dates table for slicer and creating a measure like so?
Measure = VAR StartDate = MINX ( ALLSELECTED ( Dates ), Dates[Date] ) VAR EndDate = MAXX ( ALLSELECTED ( Dates ), Dates[Date] ) VAR S1 = EOMONTH ( StartDate, -1 ) + 1 VAR S2 = EOMONTH ( StartDate, 0 ) VAR CurrentDate = MAX ( Dates[Date] ) VAR C1 = EOMONTH ( CurrentDate, -1 ) + 1 VAR C2 = EOMONTH ( CurrentDate, 0 ) VAR StartDateValue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] >= S1 && 'Table'[Date] <= S2 ) VAR CurrentDateValue = CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Date] >= C1 && 'Table'[Date] <= C2 ) RETURN IF ( MAX ( Dates[Date] ) >= StartDate && MAX ( Dates[Date] ) <= EndDate, DIVIDE ( CurrentDateValue - StartDateValue, StartDateValue ) )Use the date column from the seperate Dates table as the x axis of your line chart.
If I have a misunderstanding, please let me know.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
