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.

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

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

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

        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.