Forum Discussion

nikkirai's avatar
nikkirai
Frequent Visitor
3 years ago
Solved

Get Count of Rows Based On User Selected Current Period

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!

 

Need Help
  • nikkirai's avatar
    nikkirai
    3 years ago

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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      nikkirai
      Frequent Visitor
      3 years ago

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