Taget table distorting the visual

[Marico]

Hi Folks,

I need to create a visual where the x-axis in the bar chart represents the month-year while the y-axis shows the % op measure (I created the attached GitHub file).

Also, the model has been loaded with a target table that shows the target percentage at the end of each month.

Issue - The visual doesn't show the correct picture on adding the target % to the line chart.

The working file with sample data for reference https://github.com/priyam2790/PBI/blob/main/target.pbix 

[v-xuxinyi-msft · Accepted solution]

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.

[v-xuxinyi-msft · Accepted solution]

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.

[rajendraongole1]

Hi @Marico - cross check the relationship between the fact table and the target table is set up properly.
The relationship should be based on the Month-Year column or equivalent time dimension, ensuring a one-to-many or many-to-one relationship depending on your model.

also check the granularity of your data. The Target % values should align with the granularity of the Month-Year column.
If your fact table has daily data but the target table is at a monthly level, you might need to aggregate the measures (e.g., use AVERAGE or SUM appropriately).Handle missing values in the target table by filling gaps with 0 or BLANK().

check the above cases, if still issue persists will look into the pbix file shared. 

[Marico]

Hi @rajendraongole1 Those suggestions helped me figure out the issue. It was because of the relationship between the date dimension and target table. I have updated it based on the month-year now.

As a part of the requirement, could help me update the pbix file shared earlier to solve below two points:

Requirements:

1. In the default view, 12 month period centered on current month

i.e. we need to populate bar chart values for previous 6 months and empty bar chart values for next 6 months, even though target line should still appear for empty bar values

2. Color coding: if target line is above bar then red, if target is below bar then green and if target and bar on same line then blue.

Thanks a lot for the support.

[Ashish_Mathur]

Hi,

There is no file there.  Share the link again with the relationship corection that you have made.  If possible, show the expected result (in a Table format).

[Marico]

Hi @Ashish_Mathur  - Please check now, you should see the file in the link. I uploaded the file with corrected relationship there.

1. In the default view, 12 month period centered on current month

i.e. we need to populate bar chart values for previous 6 months and empty bar chart values for next 6 months, even though target line should still appear for empty bar values

When Jan 2025 starts, then the bars and target line should be for months 2024-07, 2024-08, 2024-09, 2024-10, 2024-11, 2024-12, 2025-01, and the next 6 months no bars, just target line. This should change dynamically for all months going forward.

2. Color coding: if target line is above bar then red, if target is below bar then green and if target and bar on same line then blue.

Let me know if I need to share more details around the requirement.

[Ashish_Mathur]

Hi,

Could you check the attached file.

Hope this helps.

[Marico]

 @Ashish_Mathur @rajendraongole1 Sorry, solution in attached file is not as per the rquirement.

Screenshot from file shared in last message by Ashish:

In the default view, 12 month period centered on current month

i.e. we need to populate bar chart values for previous 6 months and empty bar chart values for next 6 months, even though target line should still appear for empty bar values

When Jan 2025 starts, the bars and target line should be for months 2024-07, 2024-08, 2024-09, 2024-10, 2024-11, 2024-12, and 2025-01. For the next six months, there will be no bars, just a target line. This should change dynamically for all months going forward.

[v-xuxinyi-msft]

Hi @Marico 

Do the method I provided solve your problem? If so, could you please accept it as a solution? This will help more users who are facing the same or similar difficulties. Thank you!

Please feel free to let me know if there are still problems.

Best Regards,
Yulia Xu

[v-xuxinyi-msft · Accepted solution]

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.

[Marico]

Hi @v-xuxinyi-msft - Thank you so much. This fulfills the requirement. Could you tell me why did we create a separate slicer table for month-year and didn't take month-year column from date table?

However, can you also tell me

1) How to set the slicer to the current month by default?

2) How to sort the slicer mont-year in alphabetic order? I am using "YYYY-MMM" format of date in slicer.

So, the requirement is that the filter should have all the month-year sorted in alphabetical order. The selection should be on current month and year each time the report is opened.

Thank you!

[Marico]

Hi @v-xuxinyi-msft do you have a solution to above points?

[v-xuxinyi-msft · Accepted solution]

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.

[Marico]

 @v-xuxinyi-msft Thank you for looking into this quickly.

Sorry, but the values in the slicer are not sorted in chronological order. 

For example, in slicer and x-axis, it should:

Option 1 -> Jan-2024, Feb-2024, Mar-2024,....,Dec-2024, Jan-2025, Feb-2025, etc

Option 2 -> 2024-Jan, 2024-Feb, 2024-Mar,....,2024-Dec, 2025-Jan, 2025-Feb, etc

Priority is Option 1

Appreciate you looking into this issue. Thank you!

[v-xuxinyi-msft]

Hi @Marico 

You can try the following.

“Priority is Option 1”, then you need to change the format of the Month-Year column in each table to ‘MMM-YYYY’.

First is the Slicer table, in the Table View, select the Column column and sort by the Month-Year column.

For the x-axis, the Month-Year column in the Date table is currently used, but since the same Month-Year has different Date values, it cannot be sorted based on the Date column. So I used the Month-year column of the Target table as the x-axis and sorted it based on the Month_End_Date of Target column in the table.

Output:

I hope this helps you.

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.

[Marico]

 @v-xuxinyi-msft Yes, this works perfectly. Thank you for this.

There is just a final ask from the client that instead of complete Month and Year on the x-axis, we need the end-of-month date e.g. 30/6/2024, 31/7/2024, etc on a rolling basis as it was earlier.

Can I take this from the date or target table? Would this require any measure update as well? 

[v-xuxinyi-msft]

Hi @Marico 

If I understand correctly, are you needing to present the x-axis as the end of each month?

Please refer to the following test.

Create a calculated column in the Date table and then use that column as the x-axis.

EndOfMonth = EOMONTH('Date'[Date], 0)

Remove hierarchical structure

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.

[Marico · Accepted solution]

Hi @v-xuxinyi-msft 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()

                    )

                )

[v-xuxinyi-msft]

Hi @Marico 

Glad to know you solved your problem. Could you please mark useful replies and your solution as "Accept as solution"? This will help more users who are facing the same or similar difficulties. Thank you!

Best Regards,
Yulia Xu

[Ashish_Mathur]

Hi,

That is what my solution does.  Please recheck and point out the exact shortcoming.