Share via

Sales from repeat customers privous month

Stephen Ingutia 1 Reputation point
2022-10-29T22:43:08.23+00:00

Hi,

I have the below dax, but it has failed and i don't see any problem with it.

Repeat_customerSfromPMsales =
VAR currentCustomers = VALUES('Gn Sales Tb1'[case_quantity])
VAR previousMonthDates = PREVIOUSMONTH('Gn Sales Tb1'[invoice_date])
VAR previousMonthCustomers = CALCULATETABLE(VALUES('Gn Sales Tb1'[delivery_address]),
ALL('Date_tbq'[Month Name],'Date_tbq'[Month],'Date_tbq'[Year])
, previousMonthDates)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN SUMX(repeatCustomersPreviousMonth, CALCULATE(SUM('Gn Sales Tb1'[Cases])))

I need help on why i get the below error.

Feedback Type:
Frown (Error)

Timestamp:
2022-10-29T22:40:43.1425400Z

Local Time:
2022-10-30T01:40:43.1425400+03:00

Session ID:
5b7d207d-ec76-42ac-bd8e-c358c7e1f15d

Release:
October 2022

Product Version:
2. 110.805.0 (22.10) (x64)

Error Message:
MdxScript(Model) (71, 36) Function 'INTERSECT' does not support joining a column of type Number with a column of type Text.

OS Version:
Microsoft Windows NT 10.0.22000.0 (x64 en-US)

CLR Version:
4. 8 or later [Release Number = 528449]

Peak Virtual Memory:
103 GB

Private Memory:

  1. 25 GB

Peak Working Set:

  1. 27 GB

IE Version:
11. 1.22000.0

User ID:
8126f4af-6ce9-4eed-98eb-2b35ba280b90

Workbook Package Info:
1* - en-KE, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: False.

Telemetry Enabled:
True

Snapshot Trace Logs:
C:\Users\HP\Microsoft\Power BI Desktop Store App\FrownSnapShota65710a0-e29f-4d09-a7ae-46cb8db16e07.zip

Model Default Mode:
Composite

Model Version:
PowerBI_V3

Performance Trace Logs:
C:\Users\HP\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip

Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_qnaLiveConnect
PBI_azureMapVisual
PBI_compositeModelsOverAS
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_enableWebView2
PBI_sparklines

Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PQ_WebView2Connector
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_horizontalFusion
PBI_relationshipEditPane

Disabled DirectQuery Options:
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

DPI Scale:
150%

Supported Services:
Power BI

Formulas:

section Section1;

shared #"ACTIVATIONS & PEPPERTREE" = let
Source = Excel.Workbook(Web.Contents("https://savannahbrandsco-my.sharepoint.com/personal/stephen_savannahbrandsco_onmicrosoft_com/Documents/Desktop/Reports/Sales/TRADE%20ASSET%20VERIFICATION%20REPORT1.xlsb"), null, true),
#"ACTIVATIONS & PEPPERTREE1" = Source{[Name="ACTIVATIONS & PEPPERTREE"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"ACTIVATIONS & PEPPERTREE1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date Issued", type date}, {"ASSET", type text}, {"SERIAL", type text}, {"QUANTITY", Int64.Type}, {"ASSET VALUE", Int64.Type}, {"NOTES", type text}, {"CATEGORY", type text}, {"Person Responsible", type text}})
in
#"Changed Type";

shared #"TRADE ASSETS" = let
Source = Excel.Workbook(Web.Contents("https://savannahbrandsco-my.sharepoint.com/personal/stephen_savannahbrandsco_onmicrosoft_com/Documents/Desktop/Reports/Sales/TRADE%20ASSET%20VERIFICATION%20REPORT1.xlsb"), null, true),
#"TRADE ASSETS1" = Source{[Name="TRADE ASSETS"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"TRADE ASSETS1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", Int64.Type}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date Issued", type date}, {"Salesperson", type text}, {"OUTLET", type text}, {"SERIAL", type text}, {"Asset type", type text}, {"Assest No.", Int64.Type}, {"Asset Value", Int64.Type}, {"NOTES", type text}, {"Column21", type text}, {"Column22", type text}, {"Liquor House", type text}, {"2022", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column21", "Column22", "Liquor House", "2022"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date Issued", "Salesperson", "OUTLET", "SERIAL", "Asset type", "Assest No.", "Asset Value", "NOTES"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}, {"Value", "Cases Sold"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Cases Sold", Int64.Type}, {"Month", type date}})
in
#"Changed Type2";

shared #"DirectQuery to AS - SALES" = let
Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/SALES%20DATA", "SALES"),
Cubes = Table.Combine(Source[Data]),
Cube = Cubes{[Id="SALES", Kind="Cube"]}[Data]
in
Cube;

shared #"Measure Tb" = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1"})
in
#"Removed Columns";

shared Date_tbq = let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Startdate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Startdate", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "EnddateCustom", each Date.From(Date.EndOfYear(DateTime.LocalNow()))),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"EnddateCustom", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "Date", each {Number.From([Startdate])..Number.From([EnddateCustom])}),
#"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Startdate", "EnddateCustom"}),
#"Inserted Year" = Table.AddColumn(#"Removed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Week of Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Day of Week",{"Day of Week"})
in
#"Removed Columns1";

shared #"Customer FT" = let
Source = Excel.Workbook(Web.Contents("https://savannahbrandsco-my.sharepoint.com/personal/stephen_savannahbrandsco_onmicrosoft_com/Documents/Desktop/Reports/Sales/Customer%20FT.xlsb"), null, true),
#"Customer FT " = Source{[Name="Customer FT "]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Customer FT ", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Supervior", type text}, {"D.Address", type text}, {"Region", type text}, {"Area", type text}, {"Channel", type text}, {"Occasion", type text}, {"Division", type text}, {"Salesperson", type text}, {"Manager", type text}, {"Status", type text}, {"Latitude", type number}, {"Longitude", type number}, {"Listing date", type date}, {"HQ", type text}})
in
#"Changed Type";

shared #"Products Master" = let
Source = Excel.Workbook(Web.Contents("https://savannahbrandsco-my.sharepoint.com/personal/stephen_savannahbrandsco_onmicrosoft_com/Documents/Desktop/Reports/Sales/Product%20Master.xlsb"), null, true),
#"Products Master1" = Source{[Name="Products Master"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Products Master1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product/Display Name", type text}, {"Product/Name", type text}, {"Product Category", type text}, {"Product Variant", type text}, {"Packaging", type text}, {"Conversion Factor", type number}})
in
#"Changed Type";

shared #"Sales Targets (2)" = let
Source = Excel.Workbook(Web.Contents("https://savannahbrandsco-my.sharepoint.com/personal/stephen_savannahbrandsco_onmicrosoft_com/Documents/Desktop/Reports/Sales/Sales%20targets%20-%20sep.xlsb"), null, true),
#"Sales Targets1" = Source{[Name="Sales Targets"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sales Targets1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Business", type text}, {"Manager", type text}, {"Supervisor", type text}, {"Salesperson", type text}, {"Sept Vol", Int64.Type}, {"Sept GSV", Int64.Type}, {"GiN TGT", Int64.Type}, {"Listing", Int64.Type}, {"Retention", Int64.Type}, {"Column10", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column10"}),
#"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"Sept GSV", Int64.Type}, {"GiN TGT", Int64.Type}, {"Listing", Int64.Type}, {"Retention", Int64.Type}})
in
#"Changed Type1";

shared #"Kenya 2022 Holidays" = let
Source = Excel.Workbook(Web.Contents("https://savannahbrandsco-my.sharepoint.com/personal/stephen_savannahbrandsco_onmicrosoft_com/Documents/Desktop/Reports/Sales/kenya-holidays-2022-list.xlsb"), null, true),
#"Kenya 2022 Holidays1" = Source{[Name="Holidays"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Kenya 2022 Holidays1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{" DATE", type date}, {"Day name", type text}, {"NAME OF HOLIDAY", type text}}),
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
#"Removed Blank Rows";

Remote Desktop
Remote Desktop
A Microsoft app that connects remotely to computers and to virtual apps and desktops.
4,406 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
37,794 questions
0 comments