Caluclate Last Twelve Months by Power BI

[Anonymous]

Dear expert

I try to use Power BI to show the LTM sales qty as well as Last year and current year as a comparison. I try to use add column function but there's no place let me to set up LTM period.

Is any way to set up LTM condition to fill up the LTM Sales qty?

The data as below:

Thank you for your time and help.

Maggie

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

I created some data:

1. Create calculated column.

If the interval between the date and today is less than or equal to 365 days, LTM will be displayed, otherwise wait will be displayed

LT1 =
IF(ABS(DATEDIFF(TODAY(),'Table'[ALLData.date],DAY))<=365,"LTM","wait")

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Liu

This calculated column works perfectly.

Thank you so much.

Best Regards, 

Maggie

[v-yangliu-msft]

Hi  @Anonymous  ,

The customer name did not appear in the table, so I customized the rules and added a few rows of data:

Define rules:

For the date of 2020.1.1<date<=2020.1.4, give the label LTM, and [customer name] I became A[LLData.Invoice NO] field

Here are the steps you can follow：

1. Create calculated column.

LT1 =
IF('Table'[ALLData.date]<=DATE(2020,1,4)&&'Table'[ALLData.date]>=DATE(2020,1,1),"LTM","wait")

2. Create measure.

Measure =
var _select= SELECTEDVALUE('Table'[LT1])
var _1=CALCULATE(SUM('Table'[ALLData.Sales1]),FILTER('Table','Table'[LT1]=_select))
return _1

3. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Liu

Thanks for the response. 

LT1 formula is great. But i would like to do a bit adjustment - if the date within 365days ( TODAY() -[ALLData.date] <=365 days ) will shows "LTM" ; otherwise shows "wait". I try to use your formula to create the one but it doesn't work. IF('Table'[ALLData.date]<=TODAY()&&TODAY()-'Table'[ALLData.date].[Date]<=DAY(365),"LTM","wait")

Would you be able to help me for this?

Appreciate for your time and help.

Thank you so much

Maggie

[v-yangliu-msft]

Hi  @Anonymous  ,

can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

What is LTM and what is the specific result?

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Dear Liu

I would like to create a LTM column, it shows "LTM" when it is under LTM period. In this way, when I use the data for table or chart, it will grab the sales figures on sales column or qty. The raw data I am working on is huge, each single day have hundreds raw. In this way, if I want to check one customer LTM total sales qty, it will give me the right figures (by select LTM, customer name and sales qty columns).  

Appreciate your time and help.  

Maggie

[amitchandak]

@Anonymous , Not vet clear, you can have last 12 months of data using date tbale like

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Thank for your quick response.

I try the formula and it gave me the total sales qty cover that month in each column. ( as the tick below)

However, i need it to show the same qty as "AllData.Qty" as my prior email attachment. In this case when i do the table ( as the black table attached) will show the same as CY.

Sorry, i am new with Power BI and not sure what to fill up instead of "sum" in your formula. -  CALCULATE(sum(Sales[Sales Amount])

Please advise.

Thank you in advance.

Maggie