Forum Discussion

Sowmiya's avatar
Sowmiya
Helper III
7 years ago
Solved

DAX

Hi Community,

Help me with DAX to find same Week previous month Sales.

I have Date Field for multiple Years.

Thanks in advance,
Sowmiya

General Comment
Interesting Links
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Sowmiya,

     

    I have made a test with your scenario. 

     

    You could create an calendar table and then create the measure below.

     

    Measure =
VAR selectweek =
    SELECTEDVALUE ( 'Table'[WeekNum per Month] )
VAR current_month =
    SELECTEDVALUE ( 'Table 2'[Month] )
VAR previous_month =
    IF ( current_month = 1, 12, current_month - 1 )
VAR YearNo =
    IF (
        current_month = 1,
        SELECTEDVALUE ( 'Table 2'[Date].[Year] ) - 1,
        SELECTEDVALUE ( 'Table 2'[Date].[Year] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date].[Year] = YearNo
                && 'Table'[Date].[MonthNo] = previous_month
                && 'Table'[WeekNum per Month] = selectweek
        )
    )

    More details, please refer to my test pbix.

     

    Best Regards,

    Cherry

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Sowmiya,

     

    Your Question is not clear. Could you please be specific with sample data and your output. I am not getting the same week a previous month, or it should be the same week the previous year.  

     

    Best Regards,

    Ravi

     

    • Sowmiya's avatar
      Sowmiya
      Helper III
      7 years ago

      I want to find Sales for Same Week the Previous Month.
      For Example, my current day date is jan 10, 2019. 
      My Current Week Number is 2.

      Sales Output Expected for:
      Previous Month date is dec, 2018.
      Same Week Number is 2.
      Have to find Sales for Week 2 for Dec 2018.

      Current Date is Dynamic, Based on that Previous Month Same Week Sales has to be found. Is this Possible?

       

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Sowmiya,

         

        I have made a test with your scenario. 

         

        You could create an calendar table and then create the measure below.

         

        Measure =
VAR selectweek =
    SELECTEDVALUE ( 'Table'[WeekNum per Month] )
VAR current_month =
    SELECTEDVALUE ( 'Table 2'[Month] )
VAR previous_month =
    IF ( current_month = 1, 12, current_month - 1 )
VAR YearNo =
    IF (
        current_month = 1,
        SELECTEDVALUE ( 'Table 2'[Date].[Year] ) - 1,
        SELECTEDVALUE ( 'Table 2'[Date].[Year] )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date].[Year] = YearNo
                && 'Table'[Date].[MonthNo] = previous_month
                && 'Table'[WeekNum per Month] = selectweek
        )
    )

        More details, please refer to my test pbix.

         

        Best Regards,

        Cherry