Accuracy Percentage Showing Data for Current and Future Months

[rahul_ferns]

Hi Experts

I have a meaure that calculates the Accuracy % between two measures, it works but when I select the current year the results also calculates for current and future months. 
What do I need to change so the results only show data for previous months and show blank for current and future months?

Planning Accuracy % (Planned vs Actual) = 
DIVIDE(
    [0_Labour Total Actual (Assembler)] - [0_Labour Total Planned],
    [0_Labour Total Planned],
    0
)

January 2024 selected and its showing Data for the months where there is no Allocated data
I have a Calendar table with Date column. How do I use this calendar table to acheive the results?
Regards
Rah

[123abc]

To achieve the desired result of showing data only for previous months and displaying blanks for current and future months, you can modify your DAX measure to include a condition based on the calendar table. You will need to compare the selected month with the current month in your calendar table to determine whether to display the accuracy percentage or return a blank value.

Assuming you have a calendar table named Calendar and it includes a column named Date, here's how you can adjust your DAX measure:

Planning Accuracy % (Planned vs Actual) =
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Date])
VAR CurrentMonth = TODAY()
RETURN
IF(
SelectedMonth <= EOMONTH(CurrentMonth, -1), -- Check if the selected month is before or equal to the last month
DIVIDE(
[0_Labour Total Actual (Assembler)] - [0_Labour Total Planned],
[0_Labour Total Planned],
0
),
BLANK() -- Return blank for current and future months
)

In this measure:

• SelectedMonth captures the selected month from the calendar table.
• CurrentMonth gets the current month using the TODAY() function.
• The IF statement checks if the SelectedMonth is less than or equal to the last month. If it is, the accuracy percentage is calculated; otherwise, it returns a blank value.

This logic ensures that the accuracy percentage is calculated only for previous months, while current and future months display blanks.

You can replace 'Calendar'[Date] with the appropriate column reference from your calendar table if it's named differently in your Power BI model.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[rahul_ferns]

Hello Resident Rockstar

Thank you for your reply

I used your suggested measure, but the the data still appears for current and future months
Not sure why it didn't work because I do have a Calendar table with a Date column

Planning Accuracy % (Planned vs Actual) 1 = 
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Date])
VAR CurrentMonth = TODAY()
RETURN
IF(
SelectedMonth <= EOMONTH(CurrentMonth, -1),
DIVIDE(
[0_Labour Total Actual (Assembler)] - [0_Labour Total Planned],
[0_Labour Total Planned],
0
),
BLANK()
)

Regards

Rah

[123abc · Accepted solution]

It seems like your DAX measure should work properly in theory, but there might be an issue with the logic or how the visuals are interacting with your data model. Let's try a couple of adjustments:

1. Use MAX('Calendar'[Date]) instead of SELECTEDVALUE('Calendar'[Date]): Since you're dealing with a measure, it's better to use MAX function to ensure that the measure works properly across different contexts.

2. Check the Date Context: Make sure that your visualizations are filtering dates correctly from your calendar table.

Here's the adjusted measure:

Planning Accuracy % (Planned vs Actual) 1 =
VAR SelectedMonth = MAX('Calendar'[Date])
VAR CurrentMonth = TODAY()
RETURN
IF(
SelectedMonth <= EOMONTH(CurrentMonth, -1),
DIVIDE(
[0_Labour Total Actual (Assembler)] - [0_Labour Total Planned],
[0_Labour Total Planned],
0
),
BLANK()
)

This measure should now check if the selected month (filtered by the visual) is less than or equal to the last day of the previous month. If so, it calculates the accuracy percentage; otherwise, it returns a blank value.

Make sure that your visualizations are properly interacting with the date context from your calendar table. Ensure that you are not inadvertently overriding any filters in your visualizations that might be affecting the date context. If you're still encountering issues, it might be helpful to review the interactions and filters applied in your report.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[rahul_ferns]

Hello Resident Rockstar

That worked

Thank you, very much appreciated