cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sushil_Kumar777 Regular Visitor
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.

 

 

Power Bi File 

 

Capture.JPG

 

Regards,

Sushil

1 ACCEPTED SOLUTION

Accepted Solutions
Sushil_Kumar777 Regular Visitor
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 (
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

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

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

@Sushil_Kumar777 ,

 

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

Sushil_Kumar777 Regular Visitor
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:

Current Month.JPG

 

 

 

 

 

 

Previous Month Price at Item Level:

Prevoius Month.JPG

 

 

 

 

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

 

Item Level.JPG

 

 

 

 

 

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.

 

Capture.JPG

 

 

 

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

 

Sushil_Kumar777 Regular Visitor
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 .

 

Power BI New File 

 

Item Level Data for Current Month:

Item Level.JPG

 

 

 
 
 
 
Expected OUtput:
Capture.JPG
 
 
 
 
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
Sushil_Kumar777 Regular Visitor
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 (
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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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