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

How many Date columns do you have in the Fact table?

 

And how are the 2 tables related? What column in Fact to what column in Time?

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

In Fact Table I have a column Date its key for table Time, so the relationship its Fatc(Date) and Time(Date)

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

    So you don't have other date columns?

     

    I can only duplicate this issue when you are using the wrong Date column in the Fact table for your relationship to the Date table.

     

    For example if you want to SUM the Payment amounts you'll use the Payment Date (Oct 1, 2016) not Order Date (Aug 31, 2016)

     

    Meaning the relationship has to be Payment Date column to Date column in Calendar (Not Order Date to Date)

  • Twister8's avatar
    Twister8
    Icon for Helper II rankHelper II
    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)