Forum Discussion

Sam7's avatar
Sam7
Icon for Advocate II rankAdvocate II
4 years ago
Solved

Create a Previous Day, Week, Day, Year calculations

Hello,    I want to create a calculation, based on my filter created from this: https://www.youtube.com/watch?v=hilfglpKNRQ I have a daily, weekly etc filter.  I want to create a measure for the p...
Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Sam7 ,

     

    You cannot use PREVIOUSDAY('date'[Date])-7 to calculate the previous week's value. Please try the following measure.

     

    Previous Range = 
VAR _typePERIOD =
    SELECTEDVALUE ( 'Period Selection'[Type] )
VAR CurWeek =
    WEEKDAY ( MAX ( 'date'[Date] ), 2 )
RETURN
    SWITCH (
        _typePERIOD,
        "Day", CALCULATE ( SUM ( 'table'[column] ), PREVIOUSDAY ( 'date'[Date] ) ),
        "Week",
            CALCULATE (
                SUM ( 'table'[column] ),
                DATESBETWEEN (
                    'date'[Date],
                    MAX ( 'date'[Date] ) - 6 - CurWeek,
                    MAX ( 'date'[Date] ) - CurWeek
                )
            ),
        "By Month", CALCULATE ( SUM ( 'table'[column] ), PREVIOUSMONTH ( 'date'[Date] ) ),
        "By Year", CALCULATE ( SUM ( 'table'[column] ), PREVIOUSYEAR ( 'date'[Date] ) )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sam7's avatar
Sam7
Icon for Advocate II rankAdvocate II
4 years ago

Thanks for reply. My date column is the one within my results. 

 

My error is: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Sam7 ,

 

You cannot use PREVIOUSDAY('date'[Date])-7 to calculate the previous week's value. Please try the following measure.

 

Previous Range = 
VAR _typePERIOD =
    SELECTEDVALUE ( 'Period Selection'[Type] )
VAR CurWeek =
    WEEKDAY ( MAX ( 'date'[Date] ), 2 )
RETURN
    SWITCH (
        _typePERIOD,
        "Day", CALCULATE ( SUM ( 'table'[column] ), PREVIOUSDAY ( 'date'[Date] ) ),
        "Week",
            CALCULATE (
                SUM ( 'table'[column] ),
                DATESBETWEEN (
                    'date'[Date],
                    MAX ( 'date'[Date] ) - 6 - CurWeek,
                    MAX ( 'date'[Date] ) - CurWeek
                )
            ),
        "By Month", CALCULATE ( SUM ( 'table'[column] ), PREVIOUSMONTH ( 'date'[Date] ) ),
        "By Year", CALCULATE ( SUM ( 'table'[column] ), PREVIOUSYEAR ( 'date'[Date] ) )
    )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.