Specific Value Last Week Last Month

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.

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.

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?

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.

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

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,