DAX

[Sowmiya]

Hi Community,

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

I have Date Field for multiple Years.

Thanks in advance,
Sowmiya

[Anonymous]

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]

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 · Accepted solution]

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

[themistoklis]

@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

[Anonymous]

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.