cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sowmiya Member
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Highlighted
ravik4934 Member
Member

Re: DAX

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 Member
Member

Re: DAX

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?

 

ravik4934 Member
Member

Re: DAX

That means the 2nd week of December 2018 is actually Week No. 49.

 

Correct ????

If Yes, then it seems to be possible. I will update you.

themistoklis New Contributor
New Contributor

Re: DAX

@Sowmiya

 

Doesnt really make sense @Sowmiya

 

A month can have upto 4 - 6 weeks.

So for example if this month has 6 weeks how are you going to compare the 6th week? (because it wont exist on the previous month).

 

Also you are using actual sales so defintely a few of the weeks wont be full weeks.

 

I dont think you are comparing like for like

Community Support Team
Community Support Team

Re: DAX

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.