DAX formula for a measure to show difference betwe...

MiloT · ‎04-25-2022

Hi PBI Community!

Appologies for the rather basic question, I'm just starting out with PBI, I'm looking to create a new measure which should show the difference between current revenue and the total target. As per the below I have 2 seprate data sets with the values I'm looking to compare Total revenue USD which is the current state vs Region Individual targets total which is the target value.

I would really appreciate the help!

MiloT · ‎04-25-2022

@Whitewater100 for date I have a great table from https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number ) as table =>
let
FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),
WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),
InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), type logical),

InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), type number),
InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),

InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", each Date.Month([Date]), type number),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
InsertMonthName = Table.AddColumn(InsertDay, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", each try Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), type text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", each [MonthShortName] & " " & Number.ToText([Year]), type text),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), type number),
InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", each Date.EndOfMonth([Date]), type date),

InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", each Date.DayOfWeek([Date], Day.Monday) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", each Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), type text),

InsertWeekNumber= Table.AddColumn(InsertDayInitial, "ISO Weeknumber", each
if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
then Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7), type number),
InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )), Int64.Type),
BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
InsertISOqNum = Table.AddColumn(InsertISOyear, "ISO QuarterOfYear", each if [ISO Weeknumber] >39 then 4 else if [ISO Weeknumber] >26 then 3 else if [ISO Weeknumber] >13 then 2 else 1, Int64.Type),
InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO QuarterOfYear]), type text),
InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO QuarterOfYear]) & " " & Number.ToText([ISO Year]), type text),
InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10000 + [ISO QuarterOfYear] * 100, type number),
//InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])), Int64.Type),
InsertCalendarWk = Table.AddColumn(InsertISOqNy, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ),
InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100, Int64.Type),
InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),
InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], Day.Monday) < Date.From(Date.EndOfWeek(CurrentDate, Day.Monday)), type logical),
InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", each Date.EndOfWeek( [Date], Day.Monday), type date),

AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, type number),
AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [MonthOfYear] - (FYStartMonth-1) else if [MonthOfYear] >= FYStartMonth and FYStartMonth =1 then [MonthOfYear] else [MonthOfYear] + (12-FYStartMonth+1), type text),
AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number),
FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 1) ),
AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn(
Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}),
"FiscalFirstDay", each if Date.Month([Date]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
"FWStartDate", each Date.AddYears(Date.StartOfWeek( [Date], Day.Monday), 1)),
{"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
{"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
"Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
"Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
"AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
),
MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
AddFYW = Table.AddColumn( ExpandFYWeek, "Fiscal Year & Week", each if FYStartMonth =1 then [#"Week & Year"] else if Date.Month([Date]) < FYStartMonth then Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0"), type text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FYStartMonth =1 then [WeeknYear] else (if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 10000 + [Fiscal Week] * 100, Int64.Type),

InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),

CurrentDateRecord = Table.SelectRows(InsertDayType, each ([Date] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[ISO Year],
CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
CurrentYear = CurrentDateRecord{0}[Year],
CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertISOQtrOffset = Table.AddColumn(InsertDayType, "ISO QuarterOffset", each ((4 * [ISO Year]) + [ISO QuarterOfYear]) - ((4 * CurrentISOyear) + CurrentISOqtr), type number),
InsertISOYrOffset = Table.AddColumn(InsertISOQtrOffset, "ISO YearOffset", each [ISO Year] - CurrentISOyear, type number),
InsertFYoffset = Table.AddColumn(InsertISOYrOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth then CurrentYear+1 else CurrentYear) otherwise null, type number),
InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical),
InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical),
InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical),
InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),
ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"},
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
, {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ),
InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [FiscalYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),

RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}),
ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}}),
ReorderColumns = Table.ReorderColumns(ChType, {"Date", "Year", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "FiscalYearOffset", "Fiscal Quarter", "FQuarternYear", "IsCurrentFQ", "Fiscal Period", "FPeriodnYear", "IsCurrentFP", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "IsPYTD", "IsPFYTD"}, MissingField.UseNull)
in
ReorderColumns, documentation = [
Documentation.Name = " fxCalendar",
Documentation.Description = " Date table function to create an ISO-8601 calendar",
Documentation.LongDescription = " Date table function to create an ISO-8601 calendar",
Documentation.Category = " Table",
Documentation.Version = " 1.30: Fixed DayOfWeekday added Day.Monday",
Documentation.Source = " local",
Documentation.Author = " Melissa de Korte",
Documentation.Examples = { [Description = " See: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390",
Code = " Optional paramters: #(lf)
(FYStartMonthNum) Month number the fiscal year starts, Januari if omitted #(lf)
(Holidays) Select a query (and column) that contains a list of holiday dates #(lf)
(WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf)
#(lf)
Important to note: #(lf)
[Fiscal Week] starts on a Monday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf)
[IsWorkingDay] does not take holiday dates into account #(lf)
[IsBusinessDay] does take optional holiday dates into account #(lf)
[IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years",
Result = " " ] }
]
in
Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))

Whitewater100 · ‎04-25-2022

Holy Tamolie!

That's quite a Date Table. I guess your good there. Tamer is most excellent at many types of solutions.

All I can say now is does it make sense do do quarterly comparisons as it looks like you have Targets by quarter. Without playing around with your model, I will have a harder time answering correctly.

Is there anyway to make some figures up in tables to represent the problem?

Maybe see what TJ comes back with first?

Thank you..

MiloT · ‎04-25-2022

@tamerj1 The way I'm calculating Total Revenue USD is by taking raw data from Salesforce and as the export doesn't accomodate for multiple currencies I had to do a conversion as per the screenshot below.

@Whitewater100 When trying to do a simple [Total Sales] - [Sales Target] I'm getting the attached error, the total revenue is based on SF data and relates to individual opportunities, what I'm trying to get to is a list view of team members, their total opportunity revenue usd and then compare to their individual total target to get the difference.

Whitewater100 · ‎04-25-2022

Hi:

I'm not expert on SF. It looks like you may have goals on Quarter basis. Once you get the list view of people, you may want to calculate your sales on a quarterly basis to match up. You can use a QTD function.

Sales QTD = TOTALQTD([Total Sales], Dates[Date])

I do not see your date table which should be pretty important. Once you have a Date Table you go to Table tools and mark the Date column as your column to validate.

You can add the below under Modeling>New Table:

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today()

VAR FiscalMonthEnd = 6

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

Whitewater100 · ‎04-25-2022

Hi:

Usually you can do [Total Sales] - [Sales Target]. Assuming the both are meant to be compared to eachother.

Most of the times to get total sales it might look like this:

Total Sales = SUM(SalesOrders[Sales Amt]) where SalesOrders is the table name and Sales Amt is a column in the Sales Orders Table.

Total Sales might also be = SUMX(SalesOrders, SalesOrders[quantity] * SalesOrders[UnitPrice])

The Target calc should be similiar.

If you subtract sales-target , a positive answer will mean you are beatin the target and visa versa.

I hope this helps.

Note: If you have a separate date table, you should try to make a relationship based on date to your SalesOrders and Sales Target tables, so you compare for different time periods.