Blank Value and firstnonblank

[Anonymous]

Hello  -   I believe I need to be using the ISBLANK  and  FIRSTNONBLANK to solve my issue...just not quite sure how to incorporate into my current measure.  

My measure below basically works...except that if there is no value, it shows a blank.   I beleive this is connected to the "actual value" field.   And the last value that would be here (in our accounting system) would have been about 4 days ago  (we manufacture things and due to the virus have not shipped anything in four days)...so I need to find the last "nonblank" value...and that should then give me March MTD.  

The data is based on accounting ledger data that is reconciled at midnight...so the data is always one day behind.   For example, January total is calculated as of Feb 1.     Feb total is calculated as of March 1.    etc.  

Any help is appreciated!

Measure 7 =

VAR _year = SELECTEDVALUE(Dates[Year])

VAR _month = SELECTEDVALUE(Dates[MonthName])

VAR _date = CALCULATE(MIN(Dates[Date]), FILTER(ALL(Dates), Dates[Year] = _year && Dates[MonthName] = _month))

VAR _lastDate = EOMONTH(_date, -0) +1

RETURN

CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = _lastDate))

[V-pazhen-msft · Accepted solution]

@Anonymous 

Try:

Measure 7 =
CALCULATE(SUM(Flu_Snapshots[Actual Value]),FILTER(ALL('Dates'),MINX(FILTER('Dates',EARLIER([Date])+1=[Date]),Flu_Snapshots[Actual Value]))))

Best regards

Paul Zheng

[amitchandak]

@Anonymous ,Try if one of this can work

CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = lastdate(Dates[Date] )))
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = max(Dates[Date] )))
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = lastdate(Flu_Snapshots[Date] )))
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = max(Flu_Snapshots[Date] )))

[Anonymous]

Actually this measure works to give me the March MTD

last nonblank = LASTNONBLANKVALUE (

    Dates[Date],

    [Actual Value Measure]

)

Somehow I need to combine this with my previous measure. This will give me the Jan and Feb values, plus the current MTD March value.

I've tried various things but not sure how to combine these two measures (my original one, and the one above).

[Anonymous]

You mean like this?   I tried it and got an "the end of the input was reached" error message. 

RETURN

CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = lastdate(Dates[Date])),

CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = max(Dates[Date])),

CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = lastdate(Flu_Snapshots[As Of Date])),

CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = max(Flu_Snapshots[As Of Date])))