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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.