Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

LTM in Power BI

Dear expert

 

I have a question regarding LTM formula and hope anyone could help.

 

I would like to create a "LTM sales" and "LTM Qty" columns which can show "AllData.Sales" and "AllData.Qty" during LTM and show "blank" if is's unrelated. And i will be able to create a table in Power BI (as the second attached table) and looks like "CY" one.  

 

Thank you in advance.  

Capture.PNG

Maggie

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , If you have separate table - Master/Reference for Tier, Then blank means that tables missing some entries used in fact/many side table 

for ltm using date table

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
Not applicable

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

Capture 1.PNG

Please advise.

 

Thank you in advance.

 

Maggie

 

 

@Anonymous , You are creating a column, and that will sum up all values. I am suggesting a measure. Try a measure and check.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.