Card showing an unknown value

[Anonymous]

Hi Bi Community.

I have a table that has two columns:
Column 1 = week end date
Column 2 = value

Both columns 1 and 2 are pulled from an excel spreadsheet dynamically.

I'm using the measure that I've posted below but when I display this in a card it gives me a value of 5423. I have absolutely no idea where this value is coming from.

I've attached pics of the table and the last few values from the excel spreadsheet where 'Weekly average Packs Per Hr' [Weekly hourly average] pulls the data . 

The desired end result is: find last none blank value from 'Weekly average Packs Per Hr' [weekly hourly average], show this & update dynamically as the excel spreadsheet updates.

29/05/2022	3829
05/06/2022	3953
12/06/2022	3857
19/06/2022	3834
26/06/2022	3781
03/07/2022	3955
10/07/2022	2737
17/07/2022	3507
24/07/2022	3681
31/07/2022	3534
07/08/2022	3749
14/08/2022	3760
21/08/2022	3257
28/08/2022	3783
04/09/2022	3961
11/09/2022	3971
18/09/2022	3855
25/09/2022	3913
02/10/2022	4055
09/10/2022	4169
16/10/2022	4031
23/10/2022	3993
30/10/2022
06/11/2022
13/11/2022
20/11/2022
27/11/2022
04/12/2022
11/12/2022

[Anonymous]

@mangaus1111, thank you for your response. I've tried it but now getting a syntax error and cannot figure out why. Any ideas? I'm new to power Bi. Sorry. 

Measure = 
VAR _MaxDate = MAXX(
        FILTER('Weekly average Packs Per Hr',
            NOT ISBLANK('Weekly average Packs Per Hr')
        ),
'Weekly average Packs Per Hr'[Week End Date]
)
RETURN
CALCULATE(MIN('Weekly average Packs Per Hr'[Weekly hourly average]),'Weekly average Packs Per Hr'[Week End Date]=_MaxDate
)  

[mangaus1111 · Accepted solution]

I think the problem is in ISBLANK function, that requires a column as argument

NOT ISBLANK('Table'[Column])

[Anonymous]

@mangaus1111 It works.  despite the error the measure actually works when I pull it onto a card. 

thank you so much for your help. 

[Anonymous]

@mangaus1111 still no joy. same syntax error. 

Measure = 
VAR _MaxDate = MAXX(
        FILTER('Weekly average Packs Per Hr',
            NOT ISBLANK('Weekly average Packs Per Hr'[Weekly hourly average])
        ),
'Weekly average Packs Per Hr'[Week End Date]
)
RETURN
CALCULATE(MIN('Weekly average Packs Per Hr'[Weekly hourly average]),'Weekly average Packs Per Hr'[Week End Date]=_MaxDate
) 

[mangaus1111]

Hi @Anonymous ,

try this measure

Measure =

VAR _MaxDate = MAXX(

                   FILTER('Facts48',

                           NOT ISBLANK('Facts48'[Value])

                     ),

'Facts48'[Date]

)

RETURN

CALCULATE(MIN('Facts48'[Value]),'Facts48'[Date]=_MaxDate

)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

last value (1) =

VAR __A =

MAX( 'Weekly average Packs Per Hr'[Week End Date] )

VAR __B =

CALCULATE(

MAX('Weekly average Packs Per Hr'[Week End Date] ) ,

FILTER(ALLSELECTED('Weekly average Packs Per Hr'), 'Weekly average Packs Per Hr'[Week End Date] < __A && 'Weekly average Packs Per Hr'[Weekly hourly average] <> BLANK() )

)

RETURN

IF (

ISBLANK ( MAX('Weekly average Packs Per Hr'[Weekly hourly average] )),

CALCULATE( MAX( 'Weekly average Packs Per Hr'[Weekly hourly average]), FILTER(ALLSELECTED('Weekly average Packs Per Hr'), 'Weekly average Packs Per Hr'[Week End Date] = __B ) ) ,

MAX('Weekly average Packs Per Hr'[Weekly hourly average] )

)