Forum Discussion

kinga's avatar
kinga
Helper I
1 year ago
Solved

TotalYTD returning blank

I have searched extensively about this issue and have yet to see the same type of issue resolved.   I must be able to utilize a filter to return the YTD value up until a certain year/week timeframe...
Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    1 year ago

    Hi kinga,

     

    Thanks for your patience and the detailed updates!

    I now fully understand the issue, the main challenge is that when the selected Year Week (like 2025-13) has no records for a TR Type (e.g., AOG), the YTD measure returns 0, even though there were values in earlier weeks (like 13 in 2025-12).

    I’ve rewritten the DAX to better handle this. It now:

    • Finds the last non-blank week for each TR Type up to the selected week.
    • Returns the full YTD total up to that last available week, even if the current week has no data.

    Please try this revised measure:

    YTD Shipments =

VAR SelectedYearWeek = MAX ( 'Table'[YearWeek] )



VAR LastNonBlankWeek =

    CALCULATE (

        MAX ( 'Table'[YearWeek] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[YearWeek] <= SelectedYearWeek &&

            'Table'[Shipments] > 0 &&

            'Table'[TR_Type] = SELECTEDVALUE ( 'Table'[TR_Type] )

        )

    )



RETURN

    CALCULATE (

        SUM ( 'Table'[Shipments] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[YearWeek] <= LastNonBlankWeek &&

            'Table'[TR_Type] = SELECTEDVALUE ( 'Table'[TR_Type] )

        )

    ) + 0

     

    This should now return 13 for AOG even when 2025-13 has no entries.


    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

v-kpoloju-msft's avatar
v-kpoloju-msft
Community Support
1 year ago

Hi kinga,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.


Thank you.

kinga's avatar
kinga
Helper I
1 year ago

Hello, apologies for the delay. I haven't had success yet with the new calculation. It seems to only pull the current week vs YTD.

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    Hi kinga,

     

    Thanks for your patience and the detailed updates!

    I now fully understand the issue, the main challenge is that when the selected Year Week (like 2025-13) has no records for a TR Type (e.g., AOG), the YTD measure returns 0, even though there were values in earlier weeks (like 13 in 2025-12).

    I’ve rewritten the DAX to better handle this. It now:

    • Finds the last non-blank week for each TR Type up to the selected week.
    • Returns the full YTD total up to that last available week, even if the current week has no data.

    Please try this revised measure:

    YTD Shipments =

VAR SelectedYearWeek = MAX ( 'Table'[YearWeek] )



VAR LastNonBlankWeek =

    CALCULATE (

        MAX ( 'Table'[YearWeek] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[YearWeek] <= SelectedYearWeek &&

            'Table'[Shipments] > 0 &&

            'Table'[TR_Type] = SELECTEDVALUE ( 'Table'[TR_Type] )

        )

    )



RETURN

    CALCULATE (

        SUM ( 'Table'[Shipments] ),

        FILTER (

            ALL ( 'Table' ),

            'Table'[YearWeek] <= LastNonBlankWeek &&

            'Table'[TR_Type] = SELECTEDVALUE ( 'Table'[TR_Type] )

        )

    ) + 0

     

    This should now return 13 for AOG even when 2025-13 has no entries.


    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

    • v-kpoloju-msft's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      Hi kinga,

      We are following up once again regarding your query. Could you please confirm if the issue has been resolved through the provided solution.

      If the issue has been resolved, we kindly request you to share the resolution or key insights here to help others in the community. If we don’t hear back, we’ll go ahead and close this thread.

      Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

       

      Thank you for your understanding and participation.