Hi ,
Could I please ask for help for my current task.
In my report , I have slicer with financial periods and I need to make it automatically default to last completed FINANCIAL period not calendar month.
Here is an example of my slicer. I would like to see , when our period P09 finish this slicer will automatically default to period P09 and so on.
Many thanks for your help and support in advance.
Regards,
Iwona
Hi @Iwona,
If you are using EDNA’s extended date table, there are fields that end in Offset, such as MonthOffset which is 0 for the current month, -1 for the previous month and 1 for the next month.
Filtering in the Slicer by this field, we can show up to the month prior to the current one, indicating in the filter MonthOffset < 0.
In your case, we would have to generate a new field called PeriodOffSet and use it in the slicer filter.
How are these periods of a fiscal year calculated? If you can upload an example of your date table we could help you.
Greetings,
Periods of fiscal year come from Data Warehouse ( data engineers) and they are built based on fiscal calendar 4-4-5 .
Example here:
Many thanks for your help.
Iwona
Hi Iwona,
I send an example in which a conditional column has been created to the Periods table from Power Query to indicate if the period is within the range that we want to show.
The column is called IsInRange:
#“Added Conditional Column” = Table.AddColumn(#“Changed Type”, “IsInRange”, each if [End of Period] < Date.From(DateTime.LocalNow()) then 1 else 0),
Then we filter on the slicer so that it shows only the periods whose IsInRange field is 1.
Greetings,
Last Completed Financial Periods.pbix (26.6 KB)
Periodos.xlsx (9.6 KB)
Is this the only one solution ? I can not use power query , because I use Direct Query for this table and before any request I have to be 100 % sure if my proposed solution works.
Iwona
Hi @Iwona,
You can create the following measure and use it in the Slicer filter:
PeriodCompleted =
VAR _EndofPeriod =
SELECTEDVALUE( Periods[End of Period] )
VAR _Result = IF( _EndofPeriod < TODAY(), 1, 0 )
RETURN
_Result
In the slicer filter you indicate Period Completed = 1
Greetings
Last Completed Financial Periods.pbix (29.1 KB)
Thanks for working on a solution on this post @jafernandezpuga
Hello @Iwona , did the response above solve your query?
Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’
