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

processman's avatar
processman
New Member
8 years ago

You were right, it looks excellent. Now the problem I have is that by using a year filter, the "sameperiodlastyear" is no longer displayed, since the filter shows information about the chosen year but not the previous one. Is there a way to filter the current year, see the associated data of the previous year?

 

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?

     

    • MFelix's avatar
      MFelix
      Super User
      8 years ago
      Hi processman,

      Make a measure like this

      Sales totals = IF(HASONEFILTER(Table[Year]) ; [Sales previous period] ; SUMX(ALL(Table[Year]); [Sales previous year]))

      Regards
      MFelix
      • processman's avatar
        processman
        New Member
        8 years ago

        Hi MFelix!

         

        The problem with the base formula is if you define the current "currentperiod" with "max" when you totalize you get the sale of the higher period and not the sum of all the periods involved. Here my current formulas and the error in the total of each table. Thank you!

         

        Sales Last Period-Year = 
VAR CurrentPeriod =
MAX ( Consolidado[Period] )
RETURN
CALCULATE ( SUM ( Consolidado[Venta] ), Consolidado[Period] = ( CurrentPeriod - 100 ) , ALL(Consolidado[Period]), ALL(Consolidado[Year]),all(Consolidado[Tri]),all(Consolidado[Per]))
        Sales previous year = 
VAR Currentyear =
MAX ( Consolidado[Year] )
RETURN
CALCULATE ( SUM ( Consolidado[Venta] ), Consolidado[Year] = ( Currentyear - 1 ) , ALL(Consolidado[Year]), ALL(Consolidado[Period]),all(Consolidado[Tri]),all(Consolidado[Per]))
        Sales Last Year = IF(HASONEFILTER(Consolidado[Period]),[Sales Last Period-Year],IF(HASONEFILTER(Consolidado[Tri]),[Sales Last Period-Year],IF(HASONEFILTER(Consolidado[Per]),[Sales Last Period-Year],[Sales previous year])))