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.

rohit_singh's avatar
rohit_singh
Icon for Solution Sage rankSolution Sage
4 years ago

Hi Sam7 ,

The variable storing the selected values and the variables in the conditional statements are not the same

Try replacing _typeDWMY with _typePERIOD in the conditional statements.

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

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

Oops, edited my query, now showing correctly. Can you help with it now?

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Sam7 ,

    Thanks. Can you please share your current output?
    Also, please make sure you are using 'date[Date]' as the date field in your visual, else you will get blank values.

    Kind regards,

    Rohit

     

    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

    • 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.