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

Dax measure to get previous week and previous month record count

Hi,

 

Any help will be much appricatieated with following problem.

image001.jpg

 

Thanks

 

1 ACCEPTED SOLUTION
Minto
New Member

Hi,

 

My dax Measures are fixed.

 

Last Week Count =
var MaxSelectedDate= CALCULATE( MAX ( 'Date'[Date]), ALLSELECTED ( 'Date'[Date] ))
Var SelectedWeek = CALCULATE(max('Date'[Week Number]),'Date'[Date]=MaxSelectedDate)
Var SelectedYear = CALCULATE(max('Date'[Year]),'Date'[Date]=MaxSelectedDate)
Var MaxWeek = CALCULATE(max('Date'[Week Number]),ALL('Date'))


return
calculate([Total Record Count],
FILTER(ALL('Date'),IF(SelectedWeek=1,
'Date'[Week Number]=MaxWeek && 'Date'[Year]=SelectedYear-1,
'Date'[Week Number]=SelectedWeek-1 && 'Date'[Year]=SelectedYear))
)
 
 
Last Month Count =
var MaxSelectedDate= CALCULATE( MAX ('Date'[Date]), ALLSELECTED ('Date'[Date] ))
Var SelectedMonth = CALCULATE(max('Date'[Monthnumber]),'Date'[Date]=MaxSelectedDate)
Var SelectedYear = CALCULATE(max('Date'[Year]),'Date'[Date]=MaxSelectedDate)
Var MaxMonth= CALCULATE(max('Date'[Monthnumber]),ALL('Date'))


return
CALCULATE([Total Record Count],
FILTER(ALL('Date'),IF(SelectedMonth=1,
'Date'[Monthnumber]=MaxMonth && 'Date'[Year]=SelectedYear-1,
'Date'[Monthnumber]=SelectedMonth-1 && 'Date'[Year]=SelectedYear))
)
 
It's working fine.
 
Thanks
 

View solution in original post

3 REPLIES 3
Minto
New Member

Hi,

 

My dax Measures are fixed.

 

Last Week Count =
var MaxSelectedDate= CALCULATE( MAX ( 'Date'[Date]), ALLSELECTED ( 'Date'[Date] ))
Var SelectedWeek = CALCULATE(max('Date'[Week Number]),'Date'[Date]=MaxSelectedDate)
Var SelectedYear = CALCULATE(max('Date'[Year]),'Date'[Date]=MaxSelectedDate)
Var MaxWeek = CALCULATE(max('Date'[Week Number]),ALL('Date'))


return
calculate([Total Record Count],
FILTER(ALL('Date'),IF(SelectedWeek=1,
'Date'[Week Number]=MaxWeek && 'Date'[Year]=SelectedYear-1,
'Date'[Week Number]=SelectedWeek-1 && 'Date'[Year]=SelectedYear))
)
 
 
Last Month Count =
var MaxSelectedDate= CALCULATE( MAX ('Date'[Date]), ALLSELECTED ('Date'[Date] ))
Var SelectedMonth = CALCULATE(max('Date'[Monthnumber]),'Date'[Date]=MaxSelectedDate)
Var SelectedYear = CALCULATE(max('Date'[Year]),'Date'[Date]=MaxSelectedDate)
Var MaxMonth= CALCULATE(max('Date'[Monthnumber]),ALL('Date'))


return
CALCULATE([Total Record Count],
FILTER(ALL('Date'),IF(SelectedMonth=1,
'Date'[Monthnumber]=MaxMonth && 'Date'[Year]=SelectedYear-1,
'Date'[Monthnumber]=SelectedMonth-1 && 'Date'[Year]=SelectedYear))
)
 
It's working fine.
 
Thanks
 

View solution in original post

Minto
New Member

Hi Amit,

 

Thank you for you reply.

I visited your blog. It's very informative but my requirement is little bit diffrent.

I got following 3 Measures. Only "Total Record Count" is working fine other 2 producting wrong result.

 

Total Record Count = COUNT(Supplier[Supplier])
 
Last Week Count =
var MaxSelectedDate= CALCULATE( MAX ( 'Date'[Date]), ALLSELECTED ( 'Date'[Date] ))
Var SelectedWeek = CALCULATE(max('Date'[Week Number]),'Date'[Date]=MaxSelectedDate)
Var SelectedYear = CALCULATE(max('Date'[Year]),'Date'[Date]=MaxSelectedDate)
Var MaxWeek = CALCULATE(max('Date'[Week Number]),ALL('Date'))


return
COUNTX(
FILTER(ALL('Date'),IF(SelectedWeek=1,
'Date'[Week Number]=MaxWeek && 'Date'[Year]=SelectedYear-1,
'Date'[Week Number]=SelectedWeek-1 && 'Date'[Year]=SelectedYear)),
[Total Record Count])
 
Last Month Count =
var MaxSelectedDate= CALCULATE( MAX ('Date'[Date]), ALLSELECTED ('Date'[Date] ))
Var SelectedMonth = CALCULATE(max('Date'[Monthnumber]),'Date'[Date]=MaxSelectedDate)
Var SelectedYear = CALCULATE(max('Date'[Year]),'Date'[Date]=MaxSelectedDate)
Var MaxMonth= CALCULATE(max('Date'[Monthnumber]),ALL('Date'))


return
COUNTX(
FILTER(ALL('Date'),IF(SelectedMonth=1,
'Date'[Monthnumber]=MaxMonth && 'Date'[Year]=SelectedYear-1,
'Date'[Week Number]=SelectedMonth-1 && 'Date'[Year]=SelectedYear)),
[Total Record Count])
Count Last week.JPG

 

 

 

Thanks

 

amitchandak
Super User IV
Super User IV

@Minto , Refer a my blog on Week vs Week

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors