Calculate LY & LM sales without using Time Intellegence functions

[Anonymous]

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 

[v-yuta-msft]

@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.

[Greg_Deckler]

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...

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

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