Get Count of Rows Based On User Selected Current Period

[nikkirai]

Hi all,

What I need to do is to get a countrows for the current reportingperiod and a countrows for all previous reporting periods. 

The result I want is:

Month: Feb

Current Period: 2

Prior Period 10

Month: Jan

Current Period: 4

Prior Period: 6

I was trying to make a filtered table with this formula:

Current Period Count =

VAR maxdate = CALCULATE ( MAX('CalTable'[Date]), allselected(CalTable[Date]))

VAR mindate = CALCULATE ( MIN(CalTable[Date]),allselected(CalTable[Date]))

VAR filteredtable =  filter  ('Table1',[ReportingPeriod] <=maxdate && [ReportingPeriod]>=mindate)

return countrows(table1)

Is there any way I can make this work? Thank you!

[amitchandak]

@nikkirai , seem like you have date and calendar table (Joined)

Try measures like

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

[nikkirai · Accepted solution]

Thank you @amitchandak . I tweaked a little from your suggestions and it worked.

Current Period Count = COUNT(Table1[RandomNumbers])

All Prior Period Count =

VAR _StartOfThisPeriod = FIRSTDATE('CalTable'[Date])

VAR _EndOfThisPeriod = LASTDATE('CalTable'[Date])

VAR _StartOfPriorPeriod = FIRSTDATE(ALL(CalTable[Date]))

VAR _EndOfPriorPeriod = _StartOfThisPeriod-1

RETURN

CALCULATE(COUNT(Table1[RandomNumbers]), DATESBETWEEN('CalTable'[Date],_StartOfPriorPeriod,_EndOfPriorPeriod))