cancel
Showing results for
Did you mean:

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

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
Resolver IV

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)
27 REPLIES 27
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
Resolver IV

Hi @abc_777 ,

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

Solution Specialist

hi @Aburar_123 ,

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

Resolver IV

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

Solution Specialist

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

Resolver IV

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

Solution Specialist

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

Resolver IV

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)

Solution Specialist

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,

Resolver IV

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

Solution Specialist

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.

Resolver IV

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)
Solution Specialist

wow,

really working what i wanted thank you so much aburar.

Solution Specialist

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

Super User

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
Resolver IV

Hi @abc_777 ,

I dont find the option to attach the Pbix file 🙂

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

Solution Specialist

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

Solution Specialist

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

Resolver IV

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

Solution Specialist

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors