Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 

 

 

Regards,

Sushil

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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