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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
abc_777
Solution Specialist
Solution Specialist

last 6 months sale by select month from slicer

hi,

 

how to calculate the previous 6 months' sale by selecting a month from the slicer.

 

if I select November then the visual will show June to November sale

if I select august then the visual will show march to august sale

 

thanks

1 ACCEPTED SOLUTION

Sorry, it is my mistake. actually i am considering the end of month for the start date and didnt consider the entire month. for eg. if i select Sep-2021 it takes the 30-Apr-2021 as a start date but it should be 01-Apr-2021.

 

So, can you please correct the Start_Date measure as below,

 

Start_Date =
var MonthEnd = EOMONTH(MAX('Date Dim1'[Date]),-5)
return DATE(YEAR(MonthEnd),MONTH(MonthEnd),1)

View solution in original post

27 REPLIES 27
ShubhamWarang
Frequent Visitor

Hi @Aburar_123 ,
previous_date =
var Last_6_months_Sales = CALCULATE(SUM(Secondary_Sales_B2C[AmountAfterDiscount]),FILTER('Dates(S)','Dates(S)'[Date]>=[Start_Date] && 'Dates(S)'[Date]<=[End_date]))
return
Last_6_months_Sales
i have created another measure also i.e. flag
Selected Period Flag =
IF(MAX('Dates(S)'[Date])>=[Start_Date] && MAX('Dates(S)'[Date])<=[End_date],1,0)
i have added this flag measure in viusal level filter
so in slicer if i select nov-2023 it giving me only nov-2023 sales
Aburar_123
Resolver IV
Resolver IV

Hi @abc_777 ,

 

Please find the below steps,

Date Dim = CALENDARAUTO(12)
//Calculated columns in Date Dim table
Month_no = 'Date Dim'[Date].[MonthNo]
Month_Year = FORMAT('Date Dim'[Date],"MMM-yyyy")
Sorting = ('Date Dim'[Date].[Year])*100+'Date Dim'[Month_no] // Helps to sort the Month_Year Column in the Visual
 
//Date Dim1 Table
Date Dim1 = 'Date Dim'
// Calculated column in Date Dim1
Sort_by = ('Date Dim1'[Date].[Year])*100+'Date Dim1'[Month_no] // Helps to sort the Month_Year Column in the Slicer
 
// Create the below measures
Start_Date = EOMONTH(MAX('Date Dim1'[Date]),-5)
End_date = MAX('Date Dim1'[Date])
Lasgt_6_months_Sales = CALCULATE(SUM(Orders[Sales]),FILTER('Date Dim','Date Dim'[Date]>='Measure Table'[Start_Date] && 'Date Dim'[Date]<='Measure Table'[End_date]))
 
Data model should be like below,
 
Aburar_123_1-1638355380424.png

 

Month_Year Slicer is from "Date Dim1" table

 
 

Aburar_123_0-1638355123634.png

 

hi @Aburar_123 ,

thanks for your help,

 

I have done what you have showen. I have a global date calendar and a date dim2 calendar

 

but i when I select a specific month form table slicer it shows me that specific month only not the 6 months from that selected month. i give you my measure and other that you showen are just global calendar and creating column only. would be able to share me your pbi sample file so I can closelly look the issue on my side

 

 

Lasgt_6_months_Sales = CALCULATE('Measure Table'[Actual Quantity (Measure) (KG)],FILTER(BMCalendar,BMCalendar[Date]>='Date Dim1'[Start_Date] && 'BMCalendar'[Date]<= 'Date Dim1'[End_date]))
 
 

abc_777_0-1638433872214.png

 

@abc_777 , Can you share your email id to send the Pbix file.

Hi@aburar,

 

As Salamualykum,

 

I would love to share but this is company data. and so it would be good if I and you can set time and you do a remote login to my computer and check. What country are you from. we can communicate via email if you wish to. and find a suitable time for us

Hi @abc_777 , did you try with that flag measure. it should work fine if you use it

Hi @Aburar_123 ,

As Salamualykum,

 

Sorry, I don't get your point what if flag measure. I m a bit new on power bi. so your help is really appriciable. and as i said you are welcome to do anydesk to my machine. thanks for your help aburar

I had given you the below measure to use. Can you please follow the below steps,

 

Please create the below measure and place it in the visual leve filter,

 

Selected Period Flag = IF(MAX('Date Dim'[Date])>='Measure Table'[Start_Date] && MAX('Date Dim'[Date])<='Measure Table'[End_date],1,0)

Aburar_123_0-1638935936459.png

 

 

As Salamualykum Aburar,

 

yes now working. is there any way i can hide flag measure? in your pbi file its hidden and as i said i am still in the learning stage if you suggest to me how to hide flat measure then will be grateful

 

thank you so much,

just place this flag measure only in the visual filter means directly drag and drop in the visaul filter. dont use it in the table visual

Thanks, Aburar,

 

see I don't know lots of think in pbi. hope one day I will learn. yes, now it's working. 

 

I hope this would be my last query regarding this issue.

See in a measure my first-month data is not coming correct.

April data is not correct. even when starting month is may that time may data not correct. what would be the reason? this should be 31128 KG

 

even in your visual you will see starting month data is not correct. it shows less. 

 

abc_777_0-1638938331672.png

 

Sorry, it is my mistake. actually i am considering the end of month for the start date and didnt consider the entire month. for eg. if i select Sep-2021 it takes the 30-Apr-2021 as a start date but it should be 01-Apr-2021.

 

So, can you please correct the Start_Date measure as below,

 

Start_Date =
var MonthEnd = EOMONTH(MAX('Date Dim1'[Date]),-5)
return DATE(YEAR(MonthEnd),MONTH(MonthEnd),1)

wow,

 

really working what i wanted thank you so much aburar. 

 

thank you for your effot. 

its dara_z777@yahoo.com

 I have done some mistakes so results not comming. that why want to see your pbi file to correct it

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @abc_777 ,

 

I dont find the option to attach the Pbix file 🙂

Please check the below points,

 

1. Date Dim1 table should be independant table. it should not have any relation with any table.

2. Date slicer should be from 'Date Dim1'. 

Hi @Aburar_123 ,

 

I have seen the pbi you sent. thank you so much 

I have done excatly what to you did

I have measure like this

 
CALCULATE('Measure Table'[Actual Quantity (Measure) (KG)],
FILTER('Date Dim','Date Dim'[Date]>='Measure Table'[1. Start_Date] && 'Date Dim'[Date]<='Measure Table'[2. End_date]))

even my filter not working when i select month slicer. do you have time to do anydesk and see what is the actualy issue.

But i have the year 1900 how can i filter them

 

abc_777_0-1638786573293.png

 

abc_777
Solution Specialist
Solution Specialist

see i select March 2020 but the filter not working. I check the relation there is no wrong. 

 

abc_777_0-1638792996482.png

 

@abc_777 , Please create the below measure and place it in the visual leve filter,

 

Selected Period Flag = IF(MAX('Date Dim'[Date])>='Measure Table'[Start_Date] && MAX('Date Dim'[Date])<='Measure Table'[End_date],1,0)

Aburar_123_0-1638802805255.png

 

Hi,

 

yes, see when you select month Oct then it shows last 6 months from Oct.

 

but is this

 

now current month is December 2021

when you select September 2021 it will then show 6 months from September 2021. but from your current month 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.