No prior year values since nothing defined?

[ryan031581]

Here is a sample dataset and my desire for a sample table compared to what I'm getting. In this example, no color was defined in 2025. In 2027 the color could change so I need to keep track of the year and color for each state. The issue is I believe no color is there for 2025. 

[v-karpurapud]

Hi @ryan031581 

We have not received an update from you for some time. To assist you in resolving the issue, we kindly request that you provide the necessary details. Once we have this information, we will be able to address your concern effectively.

Thank you.

[v-tsaipranay]

Hi @ryan031581 ,

Thanks for reaching out to the Microsoft fabric community forum.

Could you please sharing some sample data and expected outputs, so that it could be easy for understand and give you best possible solution to resolve your issue.

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Want faster answers? How to Get Your Question Answered Quickly - Microsoft Fabric Community

Hope this helps. Please feel free to reach out for any further questions.

Thank you .

[pcoley]

Please check if the DimDate Table has dates for 2025

Please try with dummy data for 2025 at the StateColor Table (duplicating 2026 colors)

Refresh and check.

[ryan_mayu]

@ryan031581 

what if CA also has black color and the order  is 50. then what will your desire output be? what's the output for color and order current year column?

[ryan031581]

A state will have retain a color for at least the year. Ideally, I would only present this for one year at a time.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Mauro89]

Hi @ryan031581,

in your table the order table has a column date. If this is typed as date and not correctly mapped so that your DAX detect the year value of the date, your formula might not work.
I suggest extracting the year of the date column in this case (YEAR() function).

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[ryan031581]

Thanks, I have all that worked out. I keep playing with removefilters and keepfilters and changing the way the subtotals work and I'm going in circles.