cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

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.

Regards,

Sushil

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

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

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 (
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
4 REPLIES 4
Community Support Team

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

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

Regular Visitor

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

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.

Regular Visitor

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

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 .

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 (
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
Regular Visitor

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

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 (
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

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,231)