Forum Discussion
Helper I% to Plan not working
Hello,
I have a simple % to plan measure that compares revenue vs quota data.
% to Plan= DIVIDE('Data'[Actuals],SUM('Quotas'[Quota]),0)
But when unfiltered, the data should show % to plan vs the entire year but it only gives % to plan vs up to todays date. Since I have no Actuals for later dates as they have not occurred, how do i get my data to show the % to the entire years quota if i have no filter for a quarter or month?
For example: The graph below is not filtered for dates but it is showing they have almost reached their quota based on it being February. I need it to show the % to plan vs the entire year if it is unfiltered.
I will recommend you create a date and join with date table
date = Date(year(right(table[Monthyear],4)),month(left(table[Monthyear],2)),1)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
5 Replies
- amitchandak
Super User
If you do not have a date filter then both data will come for the complete duration.
Please provide more details
stephanieblamitchandak i have data for quota for the entire year. Is there a way to show the current sales data vs the whole year quota instead of just until today?
- amitchandak
Try like, you need to have date calendar
YTD Sales = CALCULATE(SUM('Quotas'[Quota]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM('Quotas'[Quota]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin