Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello All,
I have a very flat table
Product -------- Month
A ---------------- JAN
B ---------------- JAN
B ---------------- FEB
D ---------------- JAN
A ---------------- JAN
A ---------------- APR
E ---------------- JAN
How can I found out the count of products, which were sold in previous month but not is follwing month
Product A sold in Jan but not in FEB so I want COUNT of
A = 2 in JAN (becauae A was not sold in Feb but sold twice in Jan)
Do not Show B becaue it was sold in both months
I'll apprecite your help.
Solved! Go to Solution.
HI @Anonymous
You could try this calculated table
Table 2 = VAR T1 = ADDCOLUMNS('Table',"MonthAsDateTime", DATEVALUE('Table'[Month] & "-2018")) VAR LeftTable = SELECTCOLUMNS(T1,"Product",[Product],"MonthAsDateTime",[MonthAsDateTime]) VAR RightTable = SELECTCOLUMNS(T1,"Product",[Product],"MonthAsDateTime",EDATE([MonthAsDateTime],-1)) VAR Exceptions = EXCEPT(LeftTable,RightTable) RETURN GROUPBY(Exceptions,[Product],[MonthAsDateTime],"C", SUMX(CURRENTGROUP(),1))
HI @Anonymous
You could try this calculated table
Table 2 = VAR T1 = ADDCOLUMNS('Table',"MonthAsDateTime", DATEVALUE('Table'[Month] & "-2018")) VAR LeftTable = SELECTCOLUMNS(T1,"Product",[Product],"MonthAsDateTime",[MonthAsDateTime]) VAR RightTable = SELECTCOLUMNS(T1,"Product",[Product],"MonthAsDateTime",EDATE([MonthAsDateTime],-1)) VAR Exceptions = EXCEPT(LeftTable,RightTable) RETURN GROUPBY(Exceptions,[Product],[MonthAsDateTime],"C", SUMX(CURRENTGROUP(),1))
Awsome you are the man!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.