Forum Discussion

processman's avatar
processman
New Member
8 years ago
Solved

Time Intelligence doubt

I have a project where instead of dividing the year into 12 months I divide it into 18 periods. Then I have a column that joins the year and the period for example 201701 (year 2017, period 01), 2018...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi processman,

     

    Instead of measure, you could create calculated columns in this scenario.

     

    Year = LEFT(Query[Period],4)

Sales previous year Column =
CALCULATE (
    SUM ( Query[Sales] ),
    FILTER ( Query, Query[Period] = EARLIER ( Query[Period] ) - 100 )
)

     

    Best regards,

    Yuliana Gu

MFelix's avatar
MFelix
Super User
8 years ago
Change the formula.to

Sales previous year =
VAR CurrentPeriod =
MAX ( Table[Period] )
RETURN
CALCULATE ( SUM ( Table[Sales] ); Table[Period] = ( CurrentPeriod - 100 ) ; ALL(Table[Period]))
processman's avatar
processman
New Member
8 years ago

Great, now I have a new problem. When I work with years the attribute "max" in the formula is not useful. It would be ideal to return the sum of each of the periods involved (either 1 or 18). Can you help me?