Specific Value Last Week Last Month

[Anonymous]

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 multiple values in a week or month the last value should be presented in the measure.

I have created a measure POC last month,  but this will return all the values in previous month

POC_Last Month = CALCULATE([Measure];PREVIOUSMONTH('Date'[Date]))

[v-lid-msft]

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]

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]

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]

Hi,

Are you able to help with the last week calculation.

Thanks,

[v-lid-msft · Accepted solution]

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]

Hi @v-lid-msft The formula works there is only a condition that these values need to be calculated per project.

So I have project colum with a serial then a colum with the dates and finally the column with the values.

Thanks!

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, We cannot understand your data model clearly, Could you please provide a sample mockup table based on fake data and the exact expected result based on the Tables? Please don't have any Confidential Information or Real data in your reply.

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]

Hi @v-lid-msft 

Sorry for my unclear explanation but the final result should be presented as shown below.

First the data table and secondly the required presentation. So per project the calculated value as per last week, last month and last quarter.

[v-lid-msft]

Hi @Anonymous ,

We have found the mistake in formula, we can use the following formulas:

LastMonthValueFromToday = 
VAR LastDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table' ,
            AND([Date]<DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ) , 1 ),[Date]>=DATE ( YEAR ( TODAY() ), MONTH ( TODAY() )-1 , 1 ))
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[POC] ),
        FILTER ( 'Table' , [Date] = LastDay )
    )

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 (
            'Table' ,
            AND ( [Date] <= LastQuarterDay, [Date] >= FirstQuarterDay )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[POC] ),
        FILTER ( 'Table' , [Date] = LastHaveValueDay ))

LastWeekValueFromToday = 
VAR FirstDayThisWeek =
    TODAY ()
        - ( WEEKDAY ( TODAY () ) - 1 )
VAR LastDayLastWeek = FirstDayThisWeek - 1
VAR FirstDayLastWeek = LastDayLastWeek - 6
VAR LastHaveValueDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table' ,
            AND ( [Date] <= LastDayLastWeek, [Date] >= FirstDayLastWeek )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[POC] ),
        FILTER ( '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]

Hi @v-lid-msft 

Many Thanks for the new solutions, they work well!!

There is only a minor thing.

When there are at the same project multiple POC values on the same created date the measure will show the total of those POC values.

This is caused by multiples changes of the POC value at the same date.

The created date has also a time stamp in our data base.

Do you know a solution for this?

[v-lid-msft]

Hi @Anonymous ,

we can use the following formulas based on the new requirement:

LastMonthValueFromToday =
VAR LastDay =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            AND (
                [Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),
                [Date]
                    >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
            )
        )
    )
VAR LastTime =
    CALCULATE ( MAX ( 'Table'[Time] ), FILTER ( 'Table', [Date] = LastDay ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[POC] ),
        FILTER ( 'Table', AND ( [Date] = LastDay, [Time] = LastTime ) )
    )

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 ( 'Table', AND ( [Date] <= LastQuarterDay, [Date] >= FirstQuarterDay ) )
    )
VAR LastTime =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER ( 'Table', [Date] = LastHaveValueDay )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[POC] ),
        FILTER ( 'Table', AND ( [Date] = LastHaveValueDay, [Time] = LastTime ) )
    )

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

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]

@v-lid-msft  The provided solution is working well. However I want to request one adjustment in the code.

When using last week/month/quarter value only a value will be shown when there was actually a changed value in that time frame.

So for example Week-1 was 49% the value last week will show 49%. However when in Week-2 the value was 49% the formula will show now value. 

But actually the value was in Week-2 49% so also in Week-1 it was 49% as there were no changes.

Is this possible?

Thanks,

Leon

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, We cannot understand your data model clearly, Could you please provide a sample mockup table based on fake data or describle the fields of each tables and the relations between tables simply? Please don't have any Confidential Information or Real data in your reply.

Best regards,

[Anonymous]

Hi many thanks for all the provided solutions! Also very nice to have a pbix file available.

Leon

[Anonymous]

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