I have the following tables in my data model:
A calendar table
A sales table with 3 columns: Date, Client, Sales $
I want to add target sales goals for every month, so I created this target goals table:
I then made a relationship between this table and my calendar table. Also, I made a relationship between my sales table and my calendar table. However, this does not seem to allow me to use dates from my calendar table in conjunction with sales data from my sales table and goals from my goals table (it seems to be either, but not both). When I try to create a KPI using the OOTB visual, it ends up looking like this:
I lined up the "Date" field from each of my 3 tables to try and troubleshoot 'Calendar' [Date]. 'Sales' [Date]. 'Goals' [Date], and you can see that 'Sales' [Date] in the middle of this table repeats itself. It seems like there is something wrong with the relationships, but I can't figure out what. I thought since I am using my calendar table as a bridge, and both my sales and goals tables are connected to it, I wouldn't have any problems but this is not the case.
Any ideas why this might be happening and what I can do to fix? Is there a better way of setting goals?
I tried recreating the issue, and wasn't able to exactly (in my report I only have one 'Date' field repeating), but I believe this may be close enough to troubleshoot.
Here are my tables in excel table format:
DateRevenue Target
| 1/1/2019 | $4,424,663 |
| 2/1/2019 | $5,125,841 |
| 3/1/2019 | $5,332,878 |
| 4/1/2019 | $6,421,102 |
| 5/1/2019 | $4,617,133 |
| 6/1/2019 | $4,011,744 |
| 7/1/2019 | $6,293,811 |
| 8/1/2019 | $6,869,289 |
| 9/1/2019 | $6,961,624 |
| 10/1/2019 | $5,892,707 |
| 11/1/2019 | $6,374,589 |
| 12/1/2019 | $5,871,117 |
Client DateMonthly Revenue
| Client 1 | 1/31/2019 | 1365245 |
| Client 2 | 2/28/2019 | 1258976 |
| Client 3 | 3/31/2019 | 463259 |
| Client 4 | 1/31/2019 | 1452365 |
| Client 5 | 2/28/2019 | 142368 |
| Client 6 | 3/31/2019 | 2365894 |
I can't post my entire calendar table, since I'll hit the character limit, so here's a sample...
idDateIdDateLocal Date StringYearMonth NumberDayMonth Name (Long)MonthWeekdayWeekday (Short)Day of WeekQuarterQuarter (format)Day Of YearWeek NumberWeek Number (Format)
| 366 | 20190101 | 1/1/2019 | 1/1/2019 | 2019 | 1 | 1 | January | Jan | Tuesday | Tue | 2 | 1 | Q1 | 1 | 1 | W1 |
| 367 | 20190102 | 1/2/2019 | 1/2/2019 | 2019 | 1 | 2 | January | Jan | Wednesday | Wed | 3 | 1 | Q1 | 2 | 1 | W1 |
| 368 | 20190103 | 1/3/2019 | 1/3/2019 | 2019 | 1 | 3 | January | Jan | Thursday | Thu | 4 | 1 | Q1 | 3 | 1 | W1 |
| 369 | 20190104 | 1/4/2019 | 1/4/2019 | 2019 | 1 | 4 | January | Jan | Friday | Fri | 5 | 1 | Q1 | 4 | 1 | W1 |
| 370 | 20190105 | 1/5/2019 | 1/5/2019 | 2019 | 1 | 5 | January | Jan | Saturday | Sat | 6 | 1 | Q1 | 5 | 1 | W1 |
| 371 | 20190106 | 1/6/2019 | 1/6/2019 | 2019 | 1 | 6 | January | Jan | Sunday | Sun | 7 | 1 | Q1 | 6 | 1 | W1 |
| 372 | 20190107 | 1/7/2019 | 1/7/2019 | 2019 | 1 | 7 | January | Jan | Monday | Mon | 1 | 1 | Q1 | 7 | 2 | W2 |
| 373 | 20190108 | 1/8/2019 | 1/8/2019 | 2019 | 1 | 8 | January | Jan | Tuesday | Tue | 2 | 1 | Q1 | 8 | 2 | W2 |
| 374 | 20190109 | 1/9/2019 | 1/9/2019 | 2019 | 1 | 9 | January | Jan | Wednesday | Wed | 3 | 1 | Q1 | 9 | 2 | W2 |
| 375 | 20190110 | 1/10/2019 | 1/10/2019 | 2019 | 1 | 10 | January | Jan | Thursday | Thu | 4 | 1 | Q1 | 10 | 2 | W2 |
| 376 | 20190111 | 1/11/2019 | 1/11/2019 | 2019 | 1 | 11 | January | Jan | Friday | Fri | 5 | 1 | Q1 | 11 | 2 | W2 |
| 377 | 20190112 | 1/12/2019 | 1/12/2019 | 2019 | 1 | 12 | January | Jan | Saturday | Sat | 6 | 1 | Q1 | 12 | 2 | W2 |
Here are my relationships:
Thank you!
The issue here is that your Target Sales is based on the 1st of the month, while your sales is based on the end of the month. Not a problem if you granularity of your table stays at the month level, but when you go to the day level it will not make sense since only the last day of the month will have the Target Sales. So a few ways to fix this, the easiest to just add column ( or transform the current date column) in your target sales to the End of the month. Then you can use columns from your Calendar table for filters ( rows, columns, slicers, etc) with this data model:
Of course this all assumes you just have a monthly sales figures and not daily figures. If you have daily sales figures you may want to allocate the monthly target over each day or may want to see a % of total monthly sales vs. target. Or any number of ways.
The basic idea is that you need the granularity to match in your Sales and Target Tables to make sense. Then you only want to use columns from your Dimension table (in this case the calendar table) to filter those fact tables.
Hope that makes sense
That worked! You know, it's funny I thought of that, but dismissed it for some reason.
The issue I'm now running into is the KPI Value shows up as (Blank) in the KPI indicator visual. I believe this is happening because I have goals for the whole year, but don't have sales values yet? When I filter the page on either Jan, Feb, Mar of 2019 (where I have recorded sales amounts) the visual works properly, but when I only have 2019 selected, the result is (Blank). Is there any way to fix this?
If you change the following for the Total Target Sales it will be capped at the max of your sales:
Total Target Sales using EOM =
VAR __SalesLastDate =CALCULATE( LASTDATE( FactSales[Date]), ALL( FactSales[Date]))
RETURN
CALCULATE(
SUM( FactTarget[Revenue Target] ),
FactTarget[Date] <= __SalesLastDate
)
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
