Measure that calculates values on current date

[Anonymous]

There is a measure that calculates some values(underline with a red line in measure)

I need to change this calculation. For example if we have values that before current date, then we should calculate it for current month.

On the picture we have values on May, but now is July(according to max date of [period]), and I need to move this values to current date.

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Since I don't have your data, I have created a template based on the information you provided, which you can refer to.

If you want the month before this month with data,take below:

test = 
VAR current_date =
    MAX ( 'Table'[period] )
VAR pervious_value =
    CALCULATE (
        [number2],
        FILTER ( ALL ( 'Table' ), 'Table'[period] < current_date )
    )
VAR THISMONBLANK =
    CALCULATE (
        MAXX ( 'Table', [number2] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[period] < MONTH ( TODAY () )
                && [number2] <> BLANK ()
        )
    )
VAR TEST1 =
    IF (
        current_date > MONTH ( TODAY () ),
        BLANK (),
        IF (
            current_date = MONTH ( TODAY () )
                && pervious_value = BLANK (),
            THISMONBLANK,
            pervious_value
        )
    )
RETURN
    TEST1

And if you want only this month with data. Use the below :

test1 = 
VAR current_date =
    MAX ( 'Table'[period] )
VAR pervious_value =
    CALCULATE (
        [number2],
        FILTER ( ALL ( 'Table' ), 'Table'[period] < current_date )
    )
VAR THISMONBLANK =
    CALCULATE (
        MAXX ( 'Table', [number2] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[period] < MONTH ( TODAY () )
                && [number2] <> BLANK ()
        )
    )
VAR TEST1 =
    IF (
        current_date > MONTH ( TODAY () )|| current_date < MONTH ( TODAY () ),
        BLANK (),
        IF (
            current_date = MONTH ( TODAY () )
                && pervious_value = BLANK (),
            THISMONBLANK,
            pervious_value
        )
    )
RETURN
    TEST1

You could download my pbix file if you need!

Best Regards

Lucien

[amitchandak]

@Anonymous , based on what I got. Assume month are coming from Date/calendar table.

Measure for values of last available month

measure =
var _max = calculate(max(Table[Date]), filter('Table', 'Table'[Date] <= Max('Date'[Date]))
return
calculate(sum(Table[Value]), filter(all('Date') , eomonth('Date'[Date],0) = eomonth(_max,0)))