Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Simple Previous Quarter Current Quarter Table from Large Dataset

Hey All

 

I am trying to create a Table like below

Now the data set i have has contracts with their Current Segment and Previous Quarter Segment in the Data as written in the final segment column. As such I want have this simple table setup where I can use a seperate table where I ensure all these segments are already set up and then do a count if for current quarter on the current segment column and previous quarter on the previous quarter segment.

 

Looking online i cant for the life of me find something that will do this or just a simple count if function in Power BI

Need Help
  • Joe_Barry's avatar
    Joe_Barry
    2 years ago

    Hi Anonymous 

    Thanks for sharing.

    Can you create an extra table for the Segment Status? You can use the "Enter Data" feature in the ribbon. Make sure the spelling is correct as we need them to match with what is in the last two columns of your table.


    Go to the Model View and create a one to many relationship from the DIM Segment Table, Segment Column to the Final_Segment column of your table. Do the same for the Previous_Quarter_Final_Segment, this will be an inactive relationship.

    Create two measures

    Final Segment = 
CALCULATE(
    DISTINCTCOUNT('Table'[Contract ID]),
    KEEPFILTERS('Table'[Final_Segment] <> BLANK())
)
    Previous Quarter Segment = 
CALCULATE(
    DISTINCTCOUNT('Table'[Contract ID]),
    KEEPFILTERS('Table'[Previous_Quarter_Final_Segment] <> BLANK()),
    USERELATIONSHIP('DIM Segments'[Segment], 'Table'[Previous_Quarter_Final_Segment]
))



    Create a table visual and add the Segment column from the DIM Segment table, then add both measures above

     



    Hope this helps

    Joe

     

4 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    Hi Anonymous 

     

    Can you share a snaphot of the table? Especiallythe column names and just put some fake data in in one row. I'll see if I can help

    Joe

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Joe_Barry here is what the the data would look like in the underlying table:

       

      Contract IDFinal_SegmentPrevious_Quarter_Final_Segment
      XXXXXXX  
      XXXXXXX4 – Low Risk4 – Low Risk
      • Joe_Barry's avatar
        Joe_Barry
        Solution Sage
        2 years ago

        Hi Anonymous 

        Thanks for sharing.

        Can you create an extra table for the Segment Status? You can use the "Enter Data" feature in the ribbon. Make sure the spelling is correct as we need them to match with what is in the last two columns of your table.


        Go to the Model View and create a one to many relationship from the DIM Segment Table, Segment Column to the Final_Segment column of your table. Do the same for the Previous_Quarter_Final_Segment, this will be an inactive relationship.

        Create two measures

        Final Segment = 
CALCULATE(
    DISTINCTCOUNT('Table'[Contract ID]),
    KEEPFILTERS('Table'[Final_Segment] <> BLANK())
)
        Previous Quarter Segment = 
CALCULATE(
    DISTINCTCOUNT('Table'[Contract ID]),
    KEEPFILTERS('Table'[Previous_Quarter_Final_Segment] <> BLANK()),
    USERELATIONSHIP('DIM Segments'[Segment], 'Table'[Previous_Quarter_Final_Segment]
))



        Create a table visual and add the Segment column from the DIM Segment table, then add both measures above

         



        Hope this helps

        Joe