Forum Discussion

Jmenas's avatar
Jmenas
Icon for Advocate III rankAdvocate III
9 years ago
Solved

KPI indicator Blank with DAX formula

Hi,

 

I just have an issue with a Dax formula for the WTW development. The KPI indicator tends to go blank and I can't see the data. Our clients want this visual. Could you help me to improve my DAX? 

Last Week Orders = CALCULATE(SUM('Orders'[NOrders]),FILTER('Datetable', 'Datetable'[Weeknumber] = WEEKNUM(MAX('Datetable'[Date]),2)-1 && YEAR('Datetable'[Date]) = Year(MAX('Datetable'[Date]))),VALUES(Datetable[Date]))


Previous Week Orders = 
CALCULATE(SUM('Orders'[NOrders]), 
FILTER('Datetable', 'Datetable'[Weeknumber] = WEEKNUM(MAX('Datetable'[Date]),2)-2 && YEAR('Datetable'[Date]) = Year(MAX('Datetable'[Date]))),VALUES(Datetable[Date]))

My data looks like this:

 

The problem is that I have to use a trend line if I use the date table date (table relationships are working). Also the KPI visual is not so helpful. 

 

 

Thanks in advance,

J. 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Jmenas

     

    I tried similar DAX formula as yours (just remove VALUES('Datatable'[Date]) part in CALCULATE). But I haven't encountered your issue. 

     

    Previous Week total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[WeekNumber]
            = MAX ( 'Table'[WeekNumber] ) - 1
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    )
)

    Previous 2 Weeks total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[WeekNumber]
            = MAX ( 'Table'[WeekNumber] ) - 2
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    )
)

     

    Regards,

     

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Jmenas

     

    I tried similar DAX formula as yours (just remove VALUES('Datatable'[Date]) part in CALCULATE). But I haven't encountered your issue. 

     

    Previous Week total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[WeekNumber]
            = MAX ( 'Table'[WeekNumber] ) - 1
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    )
)

    Previous 2 Weeks total =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[WeekNumber]
            = MAX ( 'Table'[WeekNumber] ) - 2
            && 'Table'[Year] = MAX ( 'Table'[Year] )
    )
)

     

    Regards,

     

    • Jmenas's avatar
      Jmenas
      Icon for Advocate III rankAdvocate III
      9 years ago

      Thank you.  This way it seems to work. I had the values for the slicer.