cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Month over Month

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

1 ACCEPTED SOLUTION
Microsoft Employee

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))```

Proud to be a Datanaut!

2 REPLIES 2
Microsoft Employee

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))```

Proud to be a Datanaut!

Anonymous
Not applicable

Awsome you are the man!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors