Forum Discussion

mith_tina's avatar
mith_tina
Frequent Visitor
4 years ago

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

 

 

Thanks in advance!

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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
    https://www.youtube.com/watch?v=6LUBbvcxtKA