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.
Hi.
My organization does not follow CY precisely for monthly sales data. Sometimes the month end of current month falls on the 1st working day of the next month. Thus, I am unable to use time intellegence fuction to calculate LY & LM sales.
I tried following method to calculate LY sales.
Step 1:
lastnonblank = LASTNONBLANK('Sales Table'[Sale date], 'Sales Table'[Total Sales])
Result = 2020-03-22 [ last sale date]
Step 2:
SaleDate_LY = LOOKUPVALUE('Sales Calendar'[SalesMonthSMLY],'Sales Calendar'[ResultsDate],'Sales Table'[Sale date])
Result: 201903 [ same month last year]
Step 3:
Sales_LY = CALCULATE('F - Total Sales Query'[Total Sales],FILTER('D - Sales Calendar','D - Sales Calendar'[SalesMonth]='F - Total Sales Query'[SalesMonth_LY]))
Result = BLANK
I am assuming measures has limitations in using Filters/Calculate functions. So I am getting Blank value.
In Sales Calender, I do have following columns.
1. Sale Date - yyyy-mm-dd
2. Sale YearMonth - yyyymm
3. Sale YearMonth_LY - yyyymm
4. Sale YearMonth_LM- yyyymm
What are my options to calculate LY & LM Sales?
Thanks in Advance.
@Greg_Deckler @amitchandak @ImkeF
@Anonymous ,
Not very clear about your issue, could you please share some sample data and clarify more details about your requirement.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
@Anonymous
See if this article can help you. Basically you have to create rank or your month, qte, year etc. And use that for creating prior
Also, refer this week file where Rank has been used to create prior and rolling
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |