Calculate sales period based on current month

[elie_abidaher]

Hi

I am new in Power BI and i received a somewhat complex requirment 🙂 which i don't know how to handle

I want to calculate and display a sales period for the past n years, for example if Now month is Nov 2019 and n=3 then i want to display in a table

n     Sales Period

1     Nov 1 2018 - Nov 30 2019

2     Nov 1 2017 - Nov 30 2018

3     Nov 1 2016 - Nov 30 2017

[Ashish_Mathur]

Hi,

Please refer to my Blog article - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[parry2k]

@elie_abidaher how many n periods you want to show? Does it always take current month as the month to do calculations?

[elie_abidaher]

Hi @parry2k

The maximum n is 10 and it will always take the current month for calculations

[parry2k · Accepted solution]

@elie_abidaher solution attached, everything is done in power query. a function takes two parameters and make it happen. Feel free to change as per your need.

Would appreciate Kudos 🙂 if my solution helped.

[elie_abidaher]

hi @parry2k 

now i have a table with all my specified date ranges, how can create a link between your table and my table, in the sense that i want to create a column in my table and call it Period, that column will be populated with the proper period value from your table, after checking the Date column in my table

[elie_abidaher]

Hi
Thank you for your quick help, I will check the attached and update you

[dax]

Hi elie_abidaher, 

You could refer to above suggestions to see whether it work or not. If this is not what you want, please inform me more detailed information (such as your sample data and your expected output), then I will help you more correctly.

Best Regards,
Zoe Zhi

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

[elie_abidaher]

Hi

unfortunately, i cannot post any kind of data here, but to summarize, i have two tables one containing vehicle information and the other containing service history logs

what i was asked to do is, based on the number of years i want to display how many vehicles were sold in every period, total distinct number of vehicle that came for servicing in that year, then i want to split that total and display the count for each sales period

so the outout will be

n           SalesPeriod          TotalVehiclesSold            CameForService