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
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?
Solved! Go to 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"
)
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.
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"
)
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |