Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Maggie
@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.
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |