Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Show YTD Value Even When Filtered To A Single Week? DAX? Relationship Table?

Hello Power BI Community!

 

I'm having trouble with my Report when trying to create an omnipresent YTD measure for a KPI. I'm trying to have an Advanced Card always display the YTD value, regardless of what Date I have filtered on the Report. In other words, overriding the Date Selected in the Chiclets. Is this a Measure issue? Is it a Relationship Table Issue? Any insights would be much appreciative. Thanks!

 

YTD Measure

SL YTD % = CALCULATE([SL %], DATESYTD('Date'[Date]),FILTERS('Date'[Month Abbre]))

Value Measure

SL % = [SL UFRQty]/[SL NetOrd]
 

 Image 1: YTD Value = 50.9% when filtering for all of 2021YTD = 50.9%

 Image 2: Weekly Value = 52.4% when filtering for Week 6 of February 2021 (I NEED SL YTD% TO READ 50.9%)

Week 6 = 52.4%, but YTD should = 50.9%

 Image 3: YTD Measure

Showing YTD Measure

 

Image 4: Date Table

Date Table

 

Need Help
  • selimovd's avatar
    selimovd
    5 years ago

    Hey Anonymous ,

     

    try to solve that in the measure.

    I think the ALLEXCEPT function can help you here. With this you can remove all filters of a specific table except for the one you define in the function. As you only want to filter by year I would try to add this to calculate:

    SL YTD % =
CALCULATE(
    [SL %],
    DATESYTD( 'Date'[Date] ),
    FILTERS( 'Date'[Month Abbre] ),
    ALLEXCEPT( 'Date', 'Date'[Year] )
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi

2 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    try to solve that in the measure.

    I think the ALLEXCEPT function can help you here. With this you can remove all filters of a specific table except for the one you define in the function. As you only want to filter by year I would try to add this to calculate:

    SL YTD % =
CALCULATE(
    [SL %],
    DATESYTD( 'Date'[Date] ),
    FILTERS( 'Date'[Month Abbre] ),
    ALLEXCEPT( 'Date', 'Date'[Year] )
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you selimovd ! It works as intended now! That was quick!