Help!! - Date graph axis based on user slicer selection - display only certain periods

[Telstar]

Hi Brains - really need some help

I have a Calendar date table linked (by date) to a finance fact table that contains YTD data by month (date linked) and measures for Actuals and Budget.

My pbix contains several pages of visuals displaying the various measures at points in time, for actuals and budget inc comparisons etc
The requirement I have for one of my report pages is to display a number of month points on a line graph dependant on the slicer date chosen by the user - this slicer date selection is located on my Main page and drives the majority of the visuals in the report.

For example, If the user selects June-2021 on the Main page date selector - then on the line chart visual I need to show a percentage measure over time for the following points.

1. Full Year % Actual for "Selected Year of date selected" minus 3 years - e.g FY18 , Dec 18

2. Half Year % Actual for "Selected Year of date selected" minus 2.5 years g.g 1H19, Jun 19

3. Full Year % Actual for "Selected Year of date selected" minus 2 years - e.g FY19, Dec 19

4. Half Year % Actual for "Selected Year of date selected" minus 1.5 years g.g 1H20, Jun 20

5. Full Year % Actual for "Selected Year of date selected" minus 1 years - e.g FY20, Dec 20
6. % Actual for Selected date  - e.g. Jun 2021

7. Full Year % Budget for Selected Year - FY21

Now i have created a measure to calculate the data points based on using DATEADD function from the date selected by user.
E,g for data point 5 above, using months in the date add calc

%DisplayValue =
//using months in the DATADD function - showing point 5,6,7 as examples

var SelDate = SELECTEDVALUE(DateTable[Date]) //value from user date selector on main page

var mthSelDate = month(seldate) // month selected

var point5date= DATEADD(DateTable[Date],-mthSelDate,month)
var Point5 =

CALCULATE([0_Actual%],

All(DateTable[Date])

,point5date

)
Var Point6 =
CALCULATE([0_Actual%])

Var Point7Date = DATE(Year(SelDate),12,1)

Var Point7 =

CALCULATE([0_Actual%],

All(DateTable[Date])

,point7date

)

RETURN

SWITCH(SELECTEDVALUE(DimTrendAxis[Index]),

1,Point1,

2,Point2,

3,Point3,

4,Point4,

5,Point5,

6,Point6,

7,Point7

)

I have used a simple disconnected table to display that data using a SWITCH() for each data point.
Table is an index from 1 > 7

BUT my issue is not having a user friendly axis display based on the user selection.

So currently I have a graph that looks like this

I have no idea how to create an on fly axis label based on the user date selection given the user can select for data point 6 in month.

Note - I cannot use card visuals over the top of the data point as the solution as this graph will be applied to many % measures that do not always contain data i.e it maybe only partial data for last 3 years, missing a June data point for example

Also am not fixed on the approach above an willing to have any solution that works!

Hope this makes sense 

Any suggestions hugely appreciated as always
Thanks in advance 

Telstar

[v-yetao1-msft]

Hi @Telstar 

Has your problem been solved ? If no, please provide your sample and the result you want .

If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[Telstar · Accepted solution]

Hi Community

Couldnt come up with a slick/dynamic solution for this unfortunately, so ended up building some 'fixed' table solutions that will satisfy the requirement for next 12-18mths 

Cheers

[lbendlin]

"for one of my report pages is to display a number of month points on a line graph dependant on the slicer date chosen by the user - this slicer date selection is located on my Main page and drives the majority of the visuals in the report"

Please clarify - is that a report level filter on the filter pane, or a slicer visual on that page?  if the latter, is the slicer visual replicated on the target pages and have you enabled visual sync?  If the answer is no to both then your slicer on one page does not impact the data shown on other pages.

[Telstar]

Hi Ibendlin - thanks for looking in 

To clarify
I have a slicer on the Main page that is sync'd with all the pages in the report - it is not visable though.

Cheers

Telstar

[lbendlin]

I think you want to consider using Calculation groups. Have you done these before?

[Telstar]

Hi Ibendlin

OK - was hoping there was a 'simpler' workaround solution.
I havent used Calc Groups before but looks like a need to learn how too now.

Apprecaite the advice

Telstar