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
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?
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
Make a measure like this
Sales totals = IF(HASONEFILTER(Table[Year]) ; [Sales previous period] ; SUMX(ALL(Table[Year]); [Sales previous year]))
Regards
MFelix
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])))