Forum Discussion

Twister8's avatar
Twister8
Icon for Helper II rankHelper II
9 years ago
Solved

sum values with the last date (month)

hello everyone,   I am new in PB and DAX, I need calculate the sum of values from the last month with data on my database, for example:   month      values 2016-1     1 2016-2     5 2016-3    ...
  • Twister8's avatar
    Twister8
    9 years ago

    HI,

     

    Tks everybody for answers...

     

    I solved:

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

     

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

Twister8's avatar
Twister8
Icon for Helper II rankHelper II
9 years ago

Greg_Deckler

Tks for answer :), but isnt working for me :(

What is LastMonth? function? When I type Colum = MAX[Column] I have error because Column isnt the Date column

 

 

I am try use: 

LastMonth= CALCULATE(SUM('MYtable'[Colum With Value]); PREVIOUSMONTH('MYTableTIME'[Date]))

 

However when I typed in my expression i can't see the result, because i dont have a total

 

When I use this:

LastMonth = CALCULATE([Colum With Value];DATEADD(MYTable[Date]; -1; MONTH))

the total is all months of all years, i need just last month, in my example month 03, its possible filter the actual year?

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

LastMonth is my table name.

  • Twister8's avatar
    Twister8
    Icon for Helper II rankHelper II
    9 years ago

    Tks.

     

     

    I need show just 1, because last month its october, but its wrong

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      9 years ago

      Twister8

      Create a Month Index COLUMN like this...

       

      Month Index = INT ( CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) ) )

      Then create this MEASURE...

       

      Last Month Total =
CALCULATE (
    SUM ( TableName[ColumnName] ),
    FILTER ( 'Calendar', 'Calendar'[Month Index] = MAX ( 'Calendar'[Month Index] ) - 1 )
)

      This MEASURE should show you only the Last Month's Total.

       

      Hope this helps! :smileyhappy:

      • Twister8's avatar
        Twister8
        Icon for Helper II rankHelper II
        9 years ago

        Tks for answer..

         

        But its hard for me, i have the error in:

         

        Month Index = INT (CONCATENATE (YEAR('Time'[Date]); FORMAT ( 'Time'[Date]; "MM" ) ) )

         

        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.