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

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