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)

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!

     

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

      more details...

       

      I created Column Month Index2 with the key in Fct, because i cant use Date of Dimension Time (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.) i dont know why...

       

      Month Index2 = INT (CONCATENATE (YEAR('Fct'[Date]); FORMAT ( 'Fct'[Date]; "MM" ) ) )
      Value2 = IF('Fct'[Real] < 'Fct'[THRESHOLD_G];1;0)

      Last Month Total =
      CALCULATE (
      SUM ('Fct'[Value2]);
      FILTER ( 'Fct'; 'Fct'[Month Index] = MAX ( 'Fct'[Month Index] ) - 1 )
      )

       

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

        Add the - 1 to this formula you had posted before in a picture

         

        SumLatest =
SUMX ( FILTER ( Tempo; Tempo[Data] = MAX ( Tempo[Data] ) - 1 ); [%] )