Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
5 years ago
Solved

Previous to previous week difference

Hello all, I am on direct query mode and i need a measure. 
By below dax i have achieved bottom 4 weeks(by the help of amitchandak). Now i need % change to previus weeks.

4 weeks =
VAR _maxweek =
MAXX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Inventory-Amount] <> BLANK ()
&& 'Table'[Tyco Year Id]
= MAX ( 'Table'[Tyco Year Id] )
),
'Table'[Fiscal Week]
)
VAR _prev = _maxweek - 3
RETURN
CALCULATE (
SUM ( 'Table'[Inventory-Amount] ),
FILTER (
'Table',
'Table'[Fiscal Week] >= _prev
&& 'Table'[Fiscal Week] <= _maxweek
)
)

Kindly help me to achieve to another measure to calculate previous week amount of % change from previus week.


amitchandak  Jihwan_Kim PhilipTreacy Pragati11 Ashish_Mathur Greg_Deckler  please help

Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    Hi harshagraj 

    You need calculate the value of week 23 and then make some changes to the dax.

     

     

    % Change To Previous =
VAR previous_week =
    CALCULATE (
        [4 weeks],
        FILTER (
            ALL ( 'Table' ),
            'Table'[Fiscal Week]
                = MAX ( 'Table'[Fiscal Week] ) - 1
        )
    )
VAR val = ( [4 weeks] - previous_week ) / previous_week
RETURN
IF ( ISBLANK ( previous_week ), ([4 weeks]-week23)/week23, val )

     

     

    If you have problems with calculating the value of week 23, please provide more sample data without sensitive data.

     

    Best Regards

    Caiyun Zheng

     

5 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    5 years ago

    Hi harshagraj 

    Please try this Measure.

     

    % Change To Previous =

    VAR previous_week =

        CALCULATE (

            [4 weeks],

            FILTER (

                ALL ( 'Table' ),

                'Table'[Fiscal Week]

                    = MAX ( 'Table'[Fiscal Week] ) - 1

            )

        )

    VAR val = ( [4 weeks] - previous_week ) / previous_week

    RETURN

        IF ( ISBLANK ( previous_week ), BLANK (), val )

     

    The result looks like this:

     

     

    Best Regards

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

      • v-cazheng-msft's avatar
        v-cazheng-msft
        Community Support
        5 years ago

        Hi harshagraj 

        You need calculate the value of week 23 and then make some changes to the dax.

         

         

        % Change To Previous =
VAR previous_week =
    CALCULATE (
        [4 weeks],
        FILTER (
            ALL ( 'Table' ),
            'Table'[Fiscal Week]
                = MAX ( 'Table'[Fiscal Week] ) - 1
        )
    )
VAR val = ( [4 weeks] - previous_week ) / previous_week
RETURN
IF ( ISBLANK ( previous_week ), ([4 weeks]-week23)/week23, val )

         

         

        If you have problems with calculating the value of week 23, please provide more sample data without sensitive data.

         

        Best Regards

        Caiyun Zheng

         

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Community Support
    5 years ago

    Hi harshagraj 

    Is your problem solved? If you still have some problems, please let us known. 

     

    Best Regards,

    Caiyun Zheng