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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MiloPowerBI
Frequent Visitor

Grouping Fiscal Periods by First Week of that Period

Hello all, 

 

I'm working with a dataset that has Weekly Snapshots which show several data points for products - such as Sales Forecasts etc. Snapshots will be taken and loaded into the dataset every Sunday. I also have a Fiscal Calendar table with the appropriate relationship set up. Each fiscal Month may have 4 or 5 Snapshots, or each quarter will have 13 Snapshots, but I want to be able to create a grouping or a way to filter the data by only viewing the first snapshot for each Fiscal Month. The ultimate goal for me would be a create a page a various visualisations with a slicer that allows the end user to select a Fiscal Period or their choosing, but for me to present them with the first recorded Snapshot for that period. 

 

Any advice?

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

Hello @MiloPowerBI,

 

1. Create a calculated column in your weekly snapshot table that extracts the fiscal month and year from the snapshot date

Fiscal Month = FORMAT([Snapshot Date],"yyyy/MM")

 

2. Create a measure that calculates the earliest snapshot date for each fiscal month.

Earliest Snapshot Date = 
CALCULATE(
   MIN([Snapshot Date]),
   ALLEXCEPT('Weekly Snapshot','Weekly Snapshot'[Fiscal Month])
)

 

3. Create a calculated column in your weekly snapshot table that indicates whether each snapshot is the first snapshot for its respective fiscal month.

First Snapshot Flag = 
IF(
   [Snapshot Date] = [Earliest Snapshot Date],
   "First Snapshot",
   "Not First Snapshot"
)

 

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

4 REPLIES 4
MiloPowerBI
Frequent Visitor

Helllo Sahir Maharaj, 

 

Thank you for you detailed reply. I will be try to implement these steps later today and I will reply letting you know how I got on. Thank you very much for your assistance. 

 

Its my pleasure @MiloPowerBI. Let me know if you need further assistance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Sahir_Maharaj
Super User
Super User

Hello @MiloPowerBI,

 

1. Create a calculated column in your weekly snapshot table that extracts the fiscal month and year from the snapshot date

Fiscal Month = FORMAT([Snapshot Date],"yyyy/MM")

 

2. Create a measure that calculates the earliest snapshot date for each fiscal month.

Earliest Snapshot Date = 
CALCULATE(
   MIN([Snapshot Date]),
   ALLEXCEPT('Weekly Snapshot','Weekly Snapshot'[Fiscal Month])
)

 

3. Create a calculated column in your weekly snapshot table that indicates whether each snapshot is the first snapshot for its respective fiscal month.

First Snapshot Flag = 
IF(
   [Snapshot Date] = [Earliest Snapshot Date],
   "First Snapshot",
   "Not First Snapshot"
)

 

 


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

4. Create a slicer based on the fiscal month and year. You can use the "Fiscal Month" column you created in step 1 for this.

 

5. Create a visual that displays the data you want to show, such as a table or a chart. Use the "First Snapshot Flag" column you created in step 3 to filter the data so that only the first snapshot for each fiscal month is displayed.

 

Let me know if you might need further guidance.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.