Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Specific Value Last Week Last Month

Hi, I have a list with Values on specific dates. Now I want to create a measure which shows the value at certain time frames. So what is the value last week or last month. When there are mult...

Need Help

v-lid-msft

6 years ago

Hi Anonymous ,

Sorry for late reply, here is formula to get the value from last week of today:

LastWeekValueFromToday = 
VAR FirstDayThisWeek =
    TODAY ()
        - ( WEEKDAY ( TODAY () ) - 1 )
VAR LastDayLastWeek = FirstDayThisWeek - 1
VAR FirstDayLastWeek = LastDayLastWeek - 6
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastDayLastWeek, [Date] >= FirstDayLastWeek )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

If you want a table to get this value from selected day, we can use the following formula:

LastWeekValueFromThisday = 
VAR FirstDayThisWeek =
    MAX ( 'Table'[Date] )
        - ( WEEKDAY ( MAX ( 'Table'[Date] ) ) - 1 )
VAR LastDayLastWeek = FirstDayThisWeek - 1
VAR FirstDayLastWeek = LastDayLastWeek - 6
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastDayLastWeek, [Date] >= FirstDayLastWeek )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

Also here is last quarter from today:

LastQuaterFromToday =
VAR QuarterMonthNumber =
    (
        INT ( ( MONTH ( TODAY () ) - 1 ) / 3 ) - 1
    ) * 3 + 1
VAR LastQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( TODAY () ) - 1, 12, 31 ),
        DATE ( YEAR ( TODAY () ), QuarterMonthNumber + 2, IF ( QuarterMonthNumber = 1, 31, 30 ) )
    )
VAR FirstQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( TODAY () ) - 1, 10, 31 ),
        DATE ( YEAR ( TODAY () ), QuarterMonthNumber, IF ( QuarterMonthNumber = 4, 30, 31 ) )
    )
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastQuarterDay, [Date] >= FirstQuarterDay )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

Last Quarter Value from selected day

LastQuaterFronSelectedDay =
VAR QuarterMonthNumber =
    (
        INT ( ( MONTH ( MAX ( 'Table'[Date] ) ) - 1 ) / 3 ) - 1
    ) * 3 + 1
VAR LastQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ) - 1, 12, 31 ),
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ), QuarterMonthNumber + 2, IF ( QuarterMonthNumber = 1, 31, 30 ) )
    )
VAR FirstQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ) - 1, 10, 31 ),
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ), QuarterMonthNumber, IF ( QuarterMonthNumber = 4, 30, 31 ) )
    )
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastQuarterDay, [Date] >= FirstQuarterDay )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft

Community Support

6 years ago

Hi Anonymous ,

We can use the following measure to meet your requirement:

Measure = 
VAR selectday =
    MAX ( 'Table'[Created_date] )
VAR previous =
    DATE ( YEAR ( selectday ), MONTH ( selectday ), 1 ) - 1
VAR LastDay =
    CALCULATE (
        MAX ( 'Table'[Created_date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            MONTH ( [Created_date] ) = MONTH ( previous )
                && YEAR ( [Created_date] ) = YEAR ( previous )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sum Of Poc] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Created_date] = LastDay )
    )

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

6 years ago

Many thanks for your anwer! This looks like what I am looking for. Do you also have the formula for last week and last quarter?

v-lid-msft

Community Support

6 years ago

Hi Anonymous ,

Do you have multi years in your data? What day do you want the week begin? For example, what is the range for last week for 2019/1/1?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

6 years ago

Yes I have multiple years data but I think that is not relevant.

Last week\ Last month should be calculated as from TODAY.

Below the formula for the calendar, calculating last month and last week Amount:

let
    Source = CreateDateTable(#date(2019, 1, 1), #date(2020, 12, 31), "en-us")
in
    Source

Amount_LastMonth = CALCULATE([TotalAmount];PREVIOUSMONTH('Date'[Date]))

Amount_LastWeek = SUMX(
    FILTER(ALL('Date');
        IF(SELECTEDVALUE('Date'[WeekNumber])=1;
            'Date'[WeekNumber]=CALCULATE(MAX('Date'[WeekNumber]); ALL('Date')) && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year]))-1;"");
            'Date'[WeekNumber]=SELECTEDVALUE('Date'[WeekNumber])-1 && 'Date'[Year]=FORMAT(VALUE(SELECTEDVALUE('Date'[Year]));""))
    );
    [TotalAmount])

Anonymous

Not applicable

6 years ago

Hi,

Are you able to help with the last week calculation.

Thanks,

v-lid-msft

Community Support

6 years ago

Hi Anonymous ,

Sorry for late reply, here is formula to get the value from last week of today:

LastWeekValueFromToday = 
VAR FirstDayThisWeek =
    TODAY ()
        - ( WEEKDAY ( TODAY () ) - 1 )
VAR LastDayLastWeek = FirstDayThisWeek - 1
VAR FirstDayLastWeek = LastDayLastWeek - 6
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastDayLastWeek, [Date] >= FirstDayLastWeek )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

If you want a table to get this value from selected day, we can use the following formula:

LastWeekValueFromThisday = 
VAR FirstDayThisWeek =
    MAX ( 'Table'[Date] )
        - ( WEEKDAY ( MAX ( 'Table'[Date] ) ) - 1 )
VAR LastDayLastWeek = FirstDayThisWeek - 1
VAR FirstDayLastWeek = LastDayLastWeek - 6
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastDayLastWeek, [Date] >= FirstDayLastWeek )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

Also here is last quarter from today:

LastQuaterFromToday =
VAR QuarterMonthNumber =
    (
        INT ( ( MONTH ( TODAY () ) - 1 ) / 3 ) - 1
    ) * 3 + 1
VAR LastQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( TODAY () ) - 1, 12, 31 ),
        DATE ( YEAR ( TODAY () ), QuarterMonthNumber + 2, IF ( QuarterMonthNumber = 1, 31, 30 ) )
    )
VAR FirstQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( TODAY () ) - 1, 10, 31 ),
        DATE ( YEAR ( TODAY () ), QuarterMonthNumber, IF ( QuarterMonthNumber = 4, 30, 31 ) )
    )
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastQuarterDay, [Date] >= FirstQuarterDay )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

Last Quarter Value from selected day

LastQuaterFronSelectedDay =
VAR QuarterMonthNumber =
    (
        INT ( ( MONTH ( MAX ( 'Table'[Date] ) ) - 1 ) / 3 ) - 1
    ) * 3 + 1
VAR LastQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ) - 1, 12, 31 ),
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ), QuarterMonthNumber + 2, IF ( QuarterMonthNumber = 1, 31, 30 ) )
    )
VAR FirstQuarterDay =
    IF (
        QuarterMonthNumber = -2,
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ) - 1, 10, 31 ),
        DATE ( YEAR ( MAX ( 'Table'[Date] ) ), QuarterMonthNumber, IF ( QuarterMonthNumber = 4, 30, 31 ) )
    )
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            AND ( [Date] <= LastQuarterDay, [Date] >= FirstQuarterDay )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] = LastHaveValueDay )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Specific Value Last Week Last Month

Related content

Month over Month with multiple text values

Fabric Notebook ExitValue how to select specific values

Calculate value for a specific month

calculating specific months

Specific values in a slicer