Forum Discussion

Twister8's avatar
Twister8
Helper II
9 years ago
Solved

Show total only last month

Hi,

 

I need help

 

I need show in my gauge the value just of the LASTMONTH

 

When I use the expression:

 

= CALCULATE([VALUE];DATEADD(Tempo[Date]; -1; MONTH))

 

My result its total, its sum all values in rows, but I need just the last month

 

In this example:

month      values

2016-1     1

2016-2     5

2016-10     10

2016-10     10

 

So I need return the 20 in my result

  • Twister8's avatar
    Twister8
    9 years ago

    HI,

     

    Tks everybody for answers...

     

    I solved:

    Column

    LastMonth = IF(YEAR(TODAY())= YEAR('Fact'[Date]) && MONTH(TODAY())-1 = MONTH('Fact'[Date]);1;BLANK())

     

    Measure

    Mymeasure = CALCULATE(SUM('Fact'[Value]);'Fact'[LatMonth] =1)

6 Replies

    • Twister8's avatar
      Twister8
      Helper II
      9 years ago

      Tks for answer :)

       

      But i have a error:

       

      A3 = IF(MONTH(LASTDATE(ALL('Fct'[DATA]))) = Time[Date]
      && YEAR(LASTDATE(ALL('Fct'[DATA]))) = Time[Date];"Y";"N")

       

      Could not determine a single value for the 'Date' column in the 'Time' table. This can happen when a measure formula refers to a column containing many values, without specifying an aggregation, such as min, max, Cont or sound for a single result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Twister8,

     

    You can try to use below formula if it works on your side:

     

    Total of last month =
    var currDate=MAX(Tempo[Date])
    return
    SUMX(FILTER(ALL(Tempo),currDate>= DATE(YEAR(currDate),MONTH(currDate)-1,1)&&currDate<=DATE(YEAR(currDate),MONTH(currDate),1)-1),[VALUE])

     

    Regards,

    Xiaoxin Sheng

    • Twister8's avatar
      Twister8
      Helper II
      9 years ago

      HI,

       

      Tks everybody for answers...

       

      I solved:

      Column

      LastMonth = IF(YEAR(TODAY())= YEAR('Fact'[Date]) && MONTH(TODAY())-1 = MONTH('Fact'[Date]);1;BLANK())

       

      Measure

      Mymeasure = CALCULATE(SUM('Fact'[Value]);'Fact'[LatMonth] =1)

      • alanhanssen's avatar
        alanhanssen
        Frequent Visitor
        9 years ago

        Hi,

         

        Your formula might have failed by now as the current year is not anymore the same as YEAR('Fact'[Date]) but still is last month.

         

        I created a measure as the key for identifying

        LastMonth = if(MONTH(TODAY())-1=0,12,MONTH(TODAY()-1)) &" "& (YEAR(TODAY())-1)

         

        Also a column for identifying those.

        IsInLastMonth = IF(DateTable[MonthAndYear]=[LastMonth],TRUE(),FALSE())

  • sprather's avatar
    sprather
    Frequent Visitor
    9 years ago

    I just create a calculated column on the date field in the table:

     

    IsPreviousMonthIns = IF(Month([Inspection_Date])=Month(Today())-1,"YES","NO")

     

    Then set a visual level filter on this column to filter out the "No" results.

     

    I also create a column to check current year:

     

    IsCurrentYearIns = IF(Year([Inspection_Date])=Year(Today()),"YES","NO")

     

    These two columns allow me to pull only last months results for the current year......Works nicely....