Forum Discussion
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...
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
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])))