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.

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.

Anonymous
Not applicable
6 years ago
Hi many thanks for all the provided solutions! Also very nice to have a pbix file available.

Leon

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