Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Measure that calculates values on current date

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.

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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