Forum Discussion

dianate12's avatar
dianate12
Frequent Visitor
3 years ago

DAX

I have year 2023 and 2024 and weeks 1-52.but in week 37 i don't have values for year 2023 and for week 37 i have values for year 2024 but when i write dax im getting 36 th week values for 37 th week also for year 2023 because when i write dax with Max (week) it takes 36 th week value also there in week 37 and so when i do delta analysis its shows me zero values because 36h week values is coming for week 37th.I want to write a condition using the below dax when i don't have values for any week there should be blank and when i values for some week it should come correctly.

the dax is written below
if(SELECTEDVALUE('TEBIT analysis'[Variation])in{"New","Approved","Cancelled","Start Date Change (Postponed)","Start Date Change (Preponed)"},SUM('TEBIT analysis'[Savings]),(CALCULATE(SUM('TEBIT analysis'[Savings]),FILTER('TEBIT analysis','TEBIT analysis'[Current Tyco Week Id]=MAX('TEBIT analysis'[Current Tyco Week Id])))))

Need Help
Tips and Tricks

1 Reply

  • SamInogic's avatar
    SamInogic
    Super User
    2 years ago

    Hi dianate12 ,

    It seems that facing issue with DAX giving incorrect values when you have missing data for specific weeks in different years.

     

    Try modifying your DAX as below,
    IF (
    SELECTEDVALUE('TEBIT analysis'[Variation]) IN {"New", "Approved", "Cancelled", "Start Date Change (Postponed)", "Start Date Change (Preponed)"},
    SUM('TEBIT analysis'[Savings]),
    IF (
    ISINSCOPE('TEBIT analysis'[Current Tyco Week Id]),
    CALCULATE (
    SUM('TEBIT analysis'[Savings]),
    FILTER (
    ALL('TEBIT analysis'[Current Tyco Week Id]),
    'TEBIT analysis'[Current Tyco Week Id] = MAX('TEBIT analysis'[Current Tyco Week Id])
    )
    )
    )
    )

    Thanks!