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)

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.

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

        Month Index should be a COLUMN not Measure!