Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Last 6 month Sales trend with Date filter

Hi All,

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

Capture.PNG

 

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

 

issue.PNG

 

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 ?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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())

View solution in original post

7 REPLIES 7
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

 

 

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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 .

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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.