Forum Discussion

Anonymous's avatar
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's avatar
    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,