Taget table distorting the visual

Hi Marico 

Thanks for the reply from rajendraongole1 and Ashish_Mathur. 

If I understand correctly, your requirement is to show the bars and target line for the previous six months and and the target line for the next six months in the visualization object based on the value of the slicer? Then displays colors based on different conditions, but I need to check with you, do you need to change the color of the bar or the line?  Based on my testing, I'm afraid that if you need to change the color of the line based on different conditions, it's not possible now.

Please refer to the following test.

1. Create a calculated table as the slicer

Slicer = VALUES('Date'[Month-Year])

2. Create two measures as follow

Measure = 
VAR _selected = SELECTEDVALUE(Slicer[Month-Year])
VAR _pre6 = EOMONTH(_selected, -7) + 1
VAR _next6 = EOMONTH(_selected, 6)
RETURN
IF(MAX('Date'[Date]) >= _pre6 && MAX('Date'[Date]) <= _next6, 1, 0)

formatting = SWITCH(TRUE(),
[% op] > [fully op %], "green",
[% op] = [fully op %], "blue",
[% op] < [fully op %], "red")

3. Put the measure into the visual-level filters, set up show items when the value is 1.

As I understand it, this needs to show up to 2025/7. I have looked at your raw data and since the raw data only has data up to 2025/6, it is not possible to show data up to July on this side. If new data is added in the future, then it should show up to July.

The following is about the color setting.

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Marico 

Based on testing, since your x-axis uses the month-year column in the Date table, and since there is a relationship between the Date table and the other tables, filtering will only filter out the data that corresponds to the option, so we need a separate table. Otherwise it would look like the following:

You can create a calculated column in the Slicer table.

Column = IF(YEAR('Slicer'[Month-Year]) = YEAR(TODAY()) && MONTH('Slicer'[Month-Year]) = MONTH(TODAY()), "Current", FORMAT('Slicer'[Month-Year], "YYYY-MMM"))

The column is then used as a field for slicer.

Then modify the measure to the following:

Measure = 
VAR _selected = CALCULATE(MAX('Slicer'[Month-Year]), FILTER('Slicer', [Column] = SELECTEDVALUE('Slicer'[Column])))
VAR _pre6 = EOMONTH(_selected, -7) + 1
VAR _next6 = EOMONTH(_selected, 6)
RETURN
IF(MAX('Date'[Date]) >= _pre6 && MAX('Date'[Date]) <= _next6, 1, 0)

Output:

You need to check the current month option once, and then when you open the file in the future, the current month option will be checked by default.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous Based on my analysis, column EndOfMonth = EOMONTH('Date'[Date], 0)

wasn't giving the correct values for the bar, as I want op % on the last day of the month.

So, I created following two columns in the date table to fulfill the requirement.

endofmonth =

VAR maxsalesdate = ENDOFMONTH(sales[date])

VAR lastdateofmonth = ENDOFMONTH(Date_Table[Date])

VAR MaxSalesDateOfMonth = MIN(maxsalesdate, lastdateofmonth)

RETURN

IF (

    Date_Table[Date] = MaxSalesDateOfMonth, Date_Table[Date], BLANK()

)

End of Month = IF

                (

                    Date_Table[endofmonth] <> BLANK(), Date_Table[endofmonth],

                if(

                    Date_Table[Date]> max(sales[date]) && MONTH(MAX(sales[date])) <> MONTH(Date_Table[Date]),

                    ENDOFMONTH(Date_Table[Date]),

                    BLANK()

                    )

                )