Dynamic date table between two variable date fields

[laphroaig76]

The following:

I have a table called Period. In it are dtefrom, dteto, and period-id.

The data comes from an accounting system. Each client has a number and each individual fiscal year below it has a number again. This is linked to the period-id.

I now want that when I select an explicit fiscal year, the date table is automatically created in the background according to dte-from and dte-to.

I tried it with the following formula:
Calendar= CALENDAR(MINX(period, [dte-from]), MAXX(period, [dte-to]))

This returns me the list across all clients/fiscal years. Even in a visual where I have explicitly selected a single one. How do I get a filter of the client/fiscal year?

Thanks a lot!

Translated with www.DeepL.com/Translator (free version)

[v-jingzhang]

Hi @laphroaig76 

In Power BI, no matter you create a date table with Power Query or with DAX, it cannot be dynamically changed based on user interaction in the report. Data in a new table is only calculated when the table is created or the data model is refreshed. This is by design. Only measures can be dynamic.

So it's not possible to create dynamic date tables. You can consider using a common date table for all customers. Then create measures to calculate aggregated results according to different customers' fiscal year rules. These measures may be a little complex. 

Best Regards,
Community Support Team _ Jing

[laphroaig76]

Hi, thank you for your answer

So you're saying, that this formula

Calendar= CALENDAR(MINX(period, [dte-from]), MAXX(period, [dte-to]))

can't be extended or filtered with another parameter in the same table (period)?

[v-jingzhang]

Hi @laphroaig76 

It doesn't depend on the formula. It depends on where you use the formula. What I mean is that if you create a new table or add new columns to existing tables with DAX (or in Power Query Editor), this table or columns are filled with calculated data at once. They will keep static when you interact with slicers/filters/visuals in the report. They cannot be dynamically changed when you switch customer or fiscal years in a filter/slicer/visual in the report. 

Of course you can pass some static filters to the formula like below and use it to create a date table. It gets customer A's maximum dte-to and minimum dte-from to create a calendar table. You cannot pass dynamic parameter values from visuals to it. 

Calendar =
CALENDAR (
    MINX ( FILTER ( period, period[customer] = "customer A" ), period[dte-from] ),
    MAXX ( FILTER ( period, period[customer] = "customer A" ), period[dte-to] )
)

Dynamic table based on user interaction is not supported. 

Best Regards,
Jing

[laphroaig76]

Hi @v-jingzhang 

Thanks for the answer.
I have found a formula to create a complete date list between two dates.
In this list I also have the information of the client number and the identification number of the business year.
Now when I create a data slicer on this date list, the slicer goes from the first day to the last over all clients and fiscal years.

In the same report I have a drop down menu with the clients and a second with the fiscal years. If I change something there, the date slicer stays the same.

When I am on the date slicer, I can filter individual clients or fiscal years on the right, then it shows me the correct range in the date slicer.

So that means if I only want to see a certain range in the date slicer, I have to manually control the view on the right via the filter?

[laphroaig76]

Not really, we have totally different business years with our customers. In addition, there can be overlong business years if, for example, a company is founded in September or October and the first financial statements for fiscal reasons are prepared in December of the following year. Therefore, a business year does not always last 12 months.

[Daryl-Lynch-Bzy]

Hi @laphroaig76  - I hope the following translates properly.
If you are pulling information from Accounting system, I would suggest keeping Transaction Date.  I do not recommend period number.  If the data is extracted by Year and Month, I would still recommend converting back to date.  The date will be either the First Day of Month, or Last Day of Month.

Using date will give you the flexible to use the Date calendar hierarchy.  Have a look at the following calendar table.  It essentially contains to Financial Year heirarchies.  The standard Dec year end and March year end.

Here is the M code:

let
Source = List.Dates(#date(2020,1,1), 365.25 * 4, #duration(1,0,0,0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted End of Month" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Date]), type date),
//Remove this if you have transaction date.
#"Filtered Rows" = Table.SelectRows(#"Inserted End of Month", each ([End of Month] = [Date])),
#"Inserted Year" = Table.AddColumn(#"Filtered Rows", "Standard Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Std Month Number", each Date.Month([Date]), Int64.Type),
#"Inserted Year1" = Table.AddColumn(#"Inserted Month", "Std Year & Month", each Date.ToText([Date], "yyyyMM"), type text),
#"Inserted Year2" = Table.AddColumn(#"Inserted Year1", "March Year", each if Date.Month([Date]) < 4 then Date.Year([Date]) else Date.Year([Date]) + 1, Int64.Type),
#"Inserted Month1" = Table.AddColumn(#"Inserted Year2", "March Month Number", each if Date.Month([Date]) < 4 then Date.Month([Date]) + 9 else Date.Month([Date]) - 3 , Int64.Type),
#"Added Custom" = Table.AddColumn(#"Inserted Month1", "March Year & Period", each Text.From( [March Year] * 100 + [March Month Number] ) , type text)
in
#"Added Custom"

Does this help?