Time Intelligence doubt

[processman]

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), 201801, 201818 ...).
If for each period of each year I have "Sale $" and I need to show also the sale of the same period but of the previous year, how should I do it? I already tried "sameperiodlastyear" but it does not work properly. THANK YOU!

Ex.

Query (201701 Sale$ 500; 201801 Sale$ 1000)

In table:

201801 || (Sale$) 1000  || (Sale$"Sameperiodlastyear") 500

[v-yulgu-msft · Accepted solution]

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]

Hi @processman,

Try something like this:

Sales previous year =
VAR CurrentPeriod =
    MAX ( Table[Period] )
RETURN
    CALCULATE ( SUM ( Table[Sales] ); Table[Period] = ( CurrentPeriod - 100 ) )

This should work based on your columns and explanation.

Regards,

Mfelix

[processman]

Thanks for the answer. Actually I need something like this:

[MFelix]

Hi @processman

If you add the measure that i refer and added it to you rable.visual you.will get thst exactly same.look.

Regards,
MFelix

[processman]

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]

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]

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]

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]

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])))

[processman]