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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ 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? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ 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? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ 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? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.