Last 6 month Sales trend with Date filter

[Anonymous]

Hi All,

 I have clarification to show last 6 months sales trend with date filter . attached report below

Sales (last n months) =

Var sum1=
CALCULATE (
sum(Sales[Allocated amount] ); DATESINPERIOD ( 'Date'[Date]; MAX ( 'Date'[Date]); -6; MONTH )
)

Return sum1

Steps working:

  1. I have used this logic show Sales (last n months) .  

tables used : 1. Date 2. Sales 3. SalesOthers 

 Filter slice used : Date table quarter+Year column .

Trend table used items :  --> Values : Sales (last n months) --> Axiz : Sales(Date)

When i used the  sales tables sales amount the filter is working to show 2 quarter values above .

Whats not working :

When i sum the SUM the salesOthers table with sales data the trend showing for all years .

Sales (last n months) =

Var sum1=
CALCULATE (
sum(Sales[Allocated amount] ); DATESINPERIOD ( 'Date'[Date]; MAX ( 'Date'[Date]); -6; MONTH )
)
Var sum2=CALCULATE (
sum(SalesOthers[Gross sales eur]); DATESINPERIOD ( 'Date'[Date]; MAX ( 'Date'[Date]); -6; MONTH ))

VAR SUM3=SUM1+sum2

Return sum3

the issue is i used the trend axiz column from sales date table . If i call others fact table salesOthermeasure its showing all the data .

Any help me ?

[parry2k]

@Anonymous are you using date on x-axis from sales table, it should be from date table.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

@parry2k   If i use Date table Qtr/Year . its shwoing only selected quarter in fitler 

[amitchandak]

@Anonymous , do not use rolling 6-month formula using dates in period or dates between,

Either user relative date slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

or refer this

https://www.youtube.com/watch?v=duMSovyosXE

[Anonymous]

I have tried its not working for me . But i have also same example which is mentioned in the video link . Can i reach you somewhere . this is really big issue for me .

[v-lionel-msft]

Hi @Anonymous ,

Do you mind sharing your .pbix file? Or please show a sample data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Here i fixed below with if 2 fact measures joined

Sales (last n months) 1 =
VAR MaxFactDate =
CALCULATE ( MAX ( Sales[Invoice date] );ALL ( 'Date') )
VAR Currentdate = MAX ( 'Date'[Date] )
VAR startdate=EDATE(Currentdate;-40)

return if (MaxFactDate<=MAX('Date'[Date])&& MaxFactDate> startdate;
CALCULATE (
Sales[Sales net euro]; ALL('Date'[Year/Qtr]); FILTER(ALL('Date'[Date]);'Date'[Date]>startdate && 'Date'[Date] <=Currentdate)) ;blank())

[Anonymous]

@SqlJason  I tried using your http://sqljason.com/2018/03/display-last-n-months-selected-month-using-single-date-dimension-in-powe...

In your scenario you used only one sales fact table . If i use 2 fact table its not working . could you help on this ?