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

     

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