Dax measure to get previous week and previous month record count

[Anonymous]

Hi,

Any help will be much appricatieated with following problem.

Thanks

[Anonymous · Accepted solution]

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

[Anonymous]

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])

Thanks

[amitchandak]

@Anonymous , 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-Last-Week/ba-p/1051123