Forum Discussion
Helper ISum data between unrelated tables
Hi stephaniebl ,
did you change the m:n relationship?
You may download my PBIX file from here.
Hope this helps.Hi stephaniebl ,
are you using the date table now?
Create a sales measure...
Sales = SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])... and a YTD quick measure
https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures
Helper Imwegener when i calculate my % to quota using this formula :
% to Quota=(SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment]))/SUM('Adjust & Quota'[Quota])
It doesnt provide the % to quota vs future dates because i do not have Revenue values for the future. So i am unable to see what the % to Quota is vs the entire year, or this quarter. My data is set up by month so i have a month date table that lists 01-2020 through 12-2025.
How can i calculate my Sales revenue ((SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])) vs the quota so that i can see how they stand vs the entire year quota? Would it be
Actual Sales =CALCULATE ((SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])), DATESYTD((ENDOFYEAR('MonthTable'[Column]),122020)
I dont know if this is correct above. Then i would take that
% to Quota = (equation above )/SUM('Adjust & Quota'{Quota])
Most Valuable ProfessionalHi stephaniebl ,
change your monthTable to a complete date table and write the monthly values on the 1st of every month.
https://docs.microsoft.com/en-us/power-bi/desktop-date-tables
You can then use a quick measure to create the YTD calculation.
https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures
- mwegener
Hi stephaniebl ,
you have to add this column in the fact tables.
- CIA/HCA Cognos Re...
- Adjust & Quota
You cannot add the date table in your way because it does not filter the fact table.
- mwegener
Hi stephaniebl ,
are you using the date table now?
Create a sales measure...
Sales = SUM('CIA/HCA Cognos Revenue'[Extended Sales PMAR Amount]) + SUM('Adjust & Quota'[Adjustment])... and a YTD quick measure
https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures
- stephaniebl
mwegener Ok i created a date table , how do i add the YYYYMM Data to the table without it being Many to many relationship to my other tables? I am not sure how to add the YYYYMM to only the first of every month.
Date =ADDCOLUMNS (CALENDAR (DATE(2017,1,1), DATE(2025,12,31)),"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),"Year", YEAR ( [Date] ),"Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),"MonthNameShort", FORMAT ( [Date], "mmm" ),"MonthNameLong", FORMAT ( [Date], "mmmm" ),"DayOfWeekNumber", WEEKDAY ( [Date] ),"DayOfWeek", FORMAT ( [Date], "dddd" ),"DayOfWeekShort", FORMAT ( [Date], "ddd" ),"Quarter", "Q" & FORMAT ( [Date], "Q" ),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )) - mwegener
Hi stephaniebl
try this
Date = DATE(LEFT('Adjust & Quota'[YYYYMM],4),RIGHT('Adjust & Quota'[YYYYMM],2),1) - stephaniebl
mwegenerdo I add this to my date table as a new column?
- stephaniebl
mwegener or can i do this? I just added a date table & related my month table to it so it is 1:Many
- stephaniebl
mwegener Ah okay! Done! Now i have a date column added to both data tables that looks like this :
So now how would i create a % to Quota equation that will give me a view for the full year?
- stephaniebl
mwegener I am now using the date table instead of month table
But i receive this error when using the quick measure year to date sales & filtering by Date
- stephaniebl
mwegener Nevermind it works now based on my original measure with the date table change. Thank you!