cancel
Showing results for
Did you mean:
Frequent Visitor

## How to get current month column in calculated table

Hi,

i have 3 facts table and multiple date fileld. i have created one calculated table for 3 facts table where all total is together. now how  can i add current month and previous month column for this calculated table.

Example:

my calculated table name is EMT. i am using below dax for counting the each person total. how can i get current month total on these dax? i tried "datebetween" function which works, but i want it will change dynamically every month.

apps=if(EMT[MG]="Jon", calculate(count(table1[JR]),table1[jl] in {"L","M","N"}),

if(EMT[MG]="Vic", calculate(count(table1[JR]),table1[jl] in {"L","M","N"}),

if(EMT[MG]="Tony", calculate(count(table1[JR]),table1[jl] in {"L","M","N"})

Super User

@mith_tina , Try measure like example

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

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

or

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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e