Forum Discussion

MorganKlaif's avatar
MorganKlaif
Helper I
2 years ago
Solved

Matrix - Adding Another Value Is Forcing SUM When I Need It Based On Date

Good afternoon,   I have a matrix view that looks like the following:   "Branch" is set as the row. Branch comes from a table called Programs.   "Date Year and Month" is set as the column...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MorganKlaif ,

    The dates do differ, so they need to correspond to the year and month, try modifying the expression to:

    Goal Value = LOOKUPVALUE('2024 Goals'[GOAL], '2024 Goals'[SITE_NAME], MAX(Programs[SITE_NAME]),'2024 Goals'[DATE].[Month],MAX(Program_Regisrations[BEGIN_DATE_ONLY].[Month ]),'2024 Goals'[DATE].[Year], MAX('Program_Regisrations'[BEGIN_DATE_ONLY].[Year]))

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MorganKlaif ,

Try creating the following measure:

Goal Value = LOOKUPVALUE('2024 Goals'[Goal], '2024
Goals'[Branch], MAX(Programs[Branch]), '2024 Goals'[Date], MAX(Program_Registrations[Date Year and Month]))

Then replace the "Goals" column in the matrix with the metric you created. Make sure that the relationship between the "2024 Goals" table and the other tables is set up correctly to support the expected behavior. Since you are working with dates, make sure that the relationship can be filtered by both branch and date to allow the metric to retrieve the correct target value.

 

If this does not solve your issue, please share the following pbix file with sample data to better help you solve your problem.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

MorganKlaif's avatar
MorganKlaif
Helper I
2 years ago

Hi Anonymous Clara,

 

Thank you for getting back to me!

 

When trying to use that measure, it brings it up as blank.

 

Would it be because my relationships are wrong, or would it be because the dates for the 2024 Goals table are based on the 1st of each month (1/1/2024, 2/1/2024, etc) and the dates for the Program_Registrations table are all different based on when the member is going (Monday 1/8/2024, Tuesday 1/9/2024, etc)?

 

I created a PBIX file for you with example data. For the Program_Registrations, I only used South Mountain for the SITE_NAME/Branch, so there wasn't too much data.

 

The link to the file can be found at: https://drive.google.com/file/d/1qiy645vjmjLbWLJVfBqxQ-IEzncJOVsw/view?usp=sharing

 

Thank you for your help so far!

 

- Morgan