Get previous Time period data in calculated column | DAX

[shubhamc_002]

Hi Team,

We have 3 columns Date, Product, Amount in our table.  Our requirement is to calculate new column which should have amount of product last 1 year back and each row should have last one yaer of amount. We should not use Sameperiodlast year since time frame is not exactly 1 years back.

Date   Product    Amount    Previous Year amount

D1     P1           Val1                VAL300

D2     P1           Val2                VAL301

.       .

.       .

.       . 

.       .

.       .

D300  P1         Val300
D301  P1         VAL301

Thanks

[Anonymous · Accepted solution]

Hi @shubhamc_002 ,

Based on your description, please try:

Previous Year amount = CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Product]=EARLIER('Table'[Product]) && 'Table'[Date] =EARLIER('Table'[Date])+299))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[amitchandak]

@shubhamc_002 , Then how far is last year. Say you need Week Year behind - 364 Days. You can have measure like

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s