Filter using measure(CurrentMonth & PreviousMonth) and count the values by one level up

[Anonymous]

Hi Team,
Need your help and I have create the measure to show new and dropped items from current month at item level, now I need to show the count of new and drop items at Vendor level. Attaching the power bi file and expected output and I have tired many ways to Calculate them but no successful please find those measure in the name of New and New Test1...3.

Power Bi File 

Regards,

Sushil

[v-yuta-msft]

@Anonymous ,

Need your help and I have create the measure to show new and dropped items from current month at item level, now I need to show the count of new and drop items at Vendor level. Attaching the power bi file and expected output and I have tired many ways to Calculate them but no successful please find those measure in the name of New and New Test1...3.

---

Your requirement is not so clear. What does "new and dropped items at Vendor level" mean? Could you please clarify more details and give the logic?

Regards,

Jimmy Tao

[Anonymous]

Every  two month once we will add set of items with the price to fact table . By using the fact table I am trying to found out how many  new item has been added in the current set and dropped compared to previous month. I am able to calculate at item level but now I need the same count at Vendor Level. Please check the Power BI file for more details.

Current Month Price at Item level:

Previous Month Price at Item Level:

New, Dropped, Same Price Items measure at Item Level compared with Previous Month:

Expected Output:

Need to Count how many items has been added and removed compared to previous month set at Vendor Level.

In our Case its one  item has been added and removed for both Morri and Tesco.

Note: Data is at Two month level so created custom calander.

[Anonymous]

Please use below latest Power BI File for testing and below logic works fine but when the Data for both Last Month and Current Month if we have blank values then it still counts .

Power BI New File 

Item Level Data for Current Month:

Expected OUtput:

New =

VAR YearMonth =

SELECTEDVALUE ( Calender[YearMonthName] )

VAR MaxSequence =

CALCULATE (

MAX ( Calender[SequenceNumber] ),

Calender[YearMonthName] = YearMonth

) - 1

VAR Newlistvlaues =

SUMX (

ADDCOLUMNS(SUMMARIZE (

FactTable,

FactTable[Code]

),"CurrentPrice",IF ( AND (SUM ( FactTable[Price] ) > 0, CALCULATE (

SUM ( FactTable[Price] ),

FILTER ( ALL ( Calender ), Calender[SequenceNumber] = MaxSequence )

) = BLANK () ), 1, 0 )),[CurrentPrice]

)

RETURN

Newlistvlaues

[Anonymous · Accepted solution]

Found the Solution: I had to use CALCULATE to fix the issue.

New =

VAR YearMonth =

SELECTEDVALUE ( Calender[YearMonthName] )

VAR MaxSequence =

CALCULATE (

MAX ( Calender[SequenceNumber] ),

Calender[YearMonthName] = YearMonth

) - 1

VAR Newlistvlaues =

SUMX (

ADDCOLUMNS(SUMMARIZE (

FactTable,

FactTable[Code]

),"CurrentPrice",IF ( AND (CALCULATE (SUM ( FactTable[Price] ) )> 0, CALCULATE (

SUM ( FactTable[Price] ),

FILTER ( ALL ( Calender ), Calender[SequenceNumber] = MaxSequence )

) = BLANK () ), 1, 0 )),[CurrentPrice]

)

RETURN

Newlistvlaues