Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jmenas
Advocate III
Advocate III

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:

Visual data.PNG

 

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. 

 

Visual Problem.PNG

 

Thanks in advance,

J. 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@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] )
    )
)

55.PNG

 

Regards,

 

View solution in original post

2 REPLIES 2
v-sihou-msft
Employee
Employee

@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] )
    )
)

55.PNG

 

Regards,

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.