Grouping Fiscal Periods by First Week of that Period

[MiloPowerBI]

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?

[MiloPowerBI]

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. 

[Sahir_Maharaj]

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

[Sahir_Maharaj · Accepted solution]

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

[Sahir_Maharaj]

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.