Simple Previous Quarter Current Quarter Table from Large Dataset

[Anonymous]

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

[Joe_Barry]

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]

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

Contract ID	Final_Segment	Previous_Quarter_Final_Segment
XXXXXXX
XXXXXXX	4 – Low Risk	4 – Low Risk

[Joe_Barry · Accepted solution]

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

[Anonymous]

Absolute Legend works perfectly!!!