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 su...
  • 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)

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