Forum Discussion
Specific matrix
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:
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
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.
6 Replies
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
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.
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.
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.
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.
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.
