Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help with Previous Month Metric

Hi all,

 

I'm having trouble getting a card metric to show only the previous month's data. I'm using this as my measure:

Processes (Previous Month) = if(max('Calendar'[Date])=EOMONTH(today(),-1),CALCULATE([Count of Processes],DATESBETWEEN('Calendar'[Date],EOMONTH(today(),-2)+1,EOMONTH(today(),-1))))
 
Strangely enough, this formula has been working for me on various other time metrics on the same report but just filtered on different processes being reported on, so I am at a lost on what is different. Some of my other metrics are also just for previous month, while others are previous month and 1 year ago for the same month.
 
 
My desired outcome is a card metric that will show 389, the total for the month of September.
 
Thank you for your time,

5 Replies

  • Anonymous , Join your date with date of date table and try to use time intelligence

     

    examples

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

     

     

    or

     

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


    MTD =
    var _max = min(MAX( 'Date'[Date]) , MAXX(allselected('Table'), 'Table'[Sales Date]))
    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))
    eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , Maxx(allselected(Sales), Sales[Date]),0)

    LMTD =
    var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
    var _max = date(Year(_max1), month(_max1)-1, day(_max))
    var _min = eomonth(_max1,-2)+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))

     

    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

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

    • Anonymous's avatar
      Anonymous
      Not applicable

      amitchandak 

      I have joined the data set with the date table. Is that what you were referring to?

    • Anonymous's avatar
      Anonymous
      Not applicable

      Ashish_Mathur 

       

      Thank you. Looks like my measure included extra pieces that did not work this one.

       

      Appreciate you providing it in the pbix so I could look at