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

 

 

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.

15 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      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's avatar
        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.