last 6 months sale by select month from slicer

[abc_777]

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

[Ray_Minds]

Hi @abc_777 
Create Measure for Last 6 month # Last 6 Month sales = CALCULATE(SUM(Data[ Sales]), DATESINPERIOD('Data'[Date],MAX(Data[Date]),-6,MONTH))

[ShubhamWarang]

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]

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,

Month_Year Slicer is from "Date Dim1" table

[abc_777]

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

[Aburar_123]

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

[abc_777]

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

[Aburar_123]

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

[abc_777]

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

[Aburar_123]

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)

[abc_777]

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,

[Aburar_123]

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

[abc_777]

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. 

[Aburar_123 · 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)

[abc_777]

wow,

really working what i wanted thank you so much aburar. 

thank you for your effot. 

[abc_777]

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

[Ashish_Mathur]

Hi,

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

[Aburar_123]

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

[abc_777]

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]

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

[Aburar_123]

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