Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community,
Help me with DAX to find same Week previous month Sales.
I have Date Field for multiple Years.
Thanks in advance,
Sowmiya
Solved! Go to 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
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
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?
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |