Forum Discussion

ryan031581's avatar
ryan031581
Frequent Visitor
7 months ago
Solved

No prior year values since nothing defined?

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. 

 

 

 

Need Help

8 Replies

  • Mauro89's avatar
    Mauro89
    Icon for Super User rankSuper User
    7 months ago

    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's avatar
      ryan031581
      Frequent Visitor
      7 months ago

      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.

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 months ago

    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's avatar
      ryan031581
      Frequent Visitor
      7 months ago

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

  • pcoley's avatar
    pcoley
    Icon for Super User rankSuper User
    7 months ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 months ago

    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 .

     

     

  • v-karpurapud's avatar
    v-karpurapud
    Icon for Community Support rankCommunity Support
    7 months ago

    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.