Specific matrix

[frederic_FAURE]

Hi all,

I have a use call. 

We need to identifie if a cusomer Id is new or existing according to date selection. The rule is quite simple:

- I have the following measures:

Qty Reference Month =

VAR m = [Reference Month]

RETURN

CALCULATE (

    [Qty],

    REMOVEFILTERS ( 'Calendar' ),

    KEEPFILTERS ( 'Calendar'[MonthStart] = m )

)

Qty Before Reference Month =

VAR m = [Reference Month]

RETURN

CALCULATE(

    [Qty],

    REMOVEFILTERS( 'Calendar' ),                

    KEEPFILTERS( 'Calendar'[Date] < m )

)

and the measure 

Is New Status Reference Month =

VAR vThis   = [Qty Reference Month]

VAR vBefore = [Qty Before Reference Month]

RETURN IF ( NOT ISBLANK ( vThis ) && COALESCE ( vBefore, 0 ) = 0, 1, 0 )

and the final measure whict determines if the client is new or existing

Current Selected Status =

IF ( [Is New Status Reference Month] = 1, "New", "Existing" )

In a simple table i place customer ID and the currenct selected status works fine (I took out the total due to semi-additive measure)

The chalenge is to create a matrix table with in row the customer Id in column the Year-Month (the entire historic is not affected by date slicer) which contains the volume. At the end my of matrix should look like this : 

The tricky part is how to add the final right column which determines the status of the client for the current select.

If Someone has n indea ut will help me

Kind regards

Frédéric

[v-venuppu]

Hi @frederic_FAURE ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @frederic_FAURE ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @frederic_FAURE ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu · Accepted solution]

Hi @frederic_FAURE ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Mathur @FBergamaschi for the prompt response.

You are close - the trick is to calculate the status independently of the Year-Month context in your matrix. Keep your existing quantity measures, but add a separate one like this:

Customer Status Current Month =
VAR vThis = [Qty Reference Month]
VAR vBefore = [Qty Before Reference Month]
RETURN
IF ( NOT ISBLANK ( vThis ) && COALESCE ( vBefore, 0 ) = 0, "New", "Existing" )

Put Customer in rows, Year-Month in columns, and [Qty] in values.

Then add [Customer Status Current Month] as another value, and move it to the far-right of the matrix.

This way, the matrix shows historical volumes per month, and the last column shows New/Existing based only on the currently selected reference month.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[FBergamaschi]

Hi @frederic_FAURE 

What is your Current DAX code for the measure [ReferenceMonth]? And hiw is your model organized? Can you share the pbix?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI