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 previous period of each filter. 

 

I created this but this did not work:  Please help

 

Previous Range =
VAR _typePERIOD = (SELECTEDVALUE ('Period Selection'[Type] ))

RETURN
IF( _typePERIOD = "Day",
CALCULATE(SUM('table'[column]),PREVIOUSDAY('date'[Date])),

IF( _typePERIOD = "Week",
CALCULATE(SUM('table'[column]]),PREVIOUSDAY('date'[Date])-7),

IF( _typePERIOD = "By Month",
CALCULATE(SUM('table'[column]),PREVIOUSMONTH('date'[Date])),

IF( _typePERIOD = "By Year",
CALCULATE(SUM('table'[column]),PREVIOUSYEAR('date'[Date]))

))))

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.

5 Replies

  • 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! 🙂