Previous year & Forecast vs sales last year percentage couldn't able to fix

[Anonymous]

Hi Team ,  I am looking for your suggestion to find out bug in my previous year Dax .

My current report : 

I am facing issue while calculating sales previous year if i use normal recently giving blank value but before its was working fine : 

Sales previous year = CALCULATE ( Sales[Sales gross tp]; Dateadd('Date'[Date]);-1;Year))

So i changed my query Dax below to fix

Sales previous year = CALCULATE ( Sales[Sales gross tp]; SAMEPERIODLASTYEAR('Date'[Date]);ALL('Date')) 

--> above outputs coming

My actual objective is to find out the Forecast vs sales last year percentage : 

Forecast vs last year sales tp percentage:= DIVIDE ( Sales_Forecast[Forecast  - Sales[Sales Previous year]),
Sales[Sales Previous year])

This Dax giving output and also it producing output for future year 2021 above and giving strange % .

Condition i tried :

1. i tried to use IF condition like below 

        Forecast vs last year sales tp percentage:= IF(FIRSTNONBLANK(ALL('Date'[Date]),1)>YEAR(TODAY()),"",
DIVIDE ( Sales_Forecast[Forecast  - Sales[Sales Previous year]),Sales[Sales Previous year])

I couldn;t filter the value because i used ALL .   1. not filtering and showing same output 

Question :  1. Could you please tell why i couldnt use normal DateADD function in date table . why its need ALL function to show previous year .

question 2: please suggest how to hanlde this situation . 

Please let me know anyother inputs is needed .

[amitchandak · Accepted solution]

@Anonymous , these formula seems fine. Make your Calendar table is marked as date Table.

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

[Anonymous]

its live connection in azure analysis

[v-shex-msft]

HI @Anonymous,

I'd like to suggest you take a look at the following link to know how to use the date function to manually filter records instead of using time intelligence functions. (They required a complete calendar table and not able to do custom or nested with other time intelligence functions)

Time Intelligence "The Hard Way" (TITHW)  

If above not help, please share some dummy data to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng