Creating a DATE calculated column that viewers can change, similar to a Tableau parameter

[BW40]

Is there a way to create a date calculated column that viewers can change themselves? Coming from a Tableau background, I have a number of calculated columns across multiple sheets that are all built using the same two date calculated columns. Changing two date columns is signficantly less likely to cause errors than risking my viewers missing one of the multiple filters on every page without noticing. Finding a way for them to be able to do it themselves will save me having to change the dates for every user any time they need to change the date range.

Date slicers can only select a range of dates rather than having an ISBLANK criteria or being open ended. I will include my date calcs and one out of a list of calcualted columns using these dates below:

1. Fiscal Year Start = DATE(2022, 07, 01)

2. Fiscal Year Stop = DATE( 2023, 06, 30)

3. Clients served in year= IF( [service application date] <= [Fiscal year End]  &&  [service stop date] >= [Fiscal Year Start] ||

[service application date] <= [Fiscal year End] && ISBLANK([service stop date]), 1, 0)

[TomMartens]

Hey  , 

there still is a fundamental difference between Power BI and Tableau, and this difference is the data model that is fueling the analytical capabilities of Power BI.

For this reason, you should aim to understand this difference first before you try to migrate Tableau best practices to Power BI. Then you will realize that Power Query (the data shaping gem) in Power BI provides parameters that you can use inside Power Query queries to calculate the depending columns.

Hopefully, this provides you with some ideas on how to tackle your challenge. This article provides an intorduction to paramter in Power Query: https://learn.microsoft.com/en-us/power-query/power-query-query-parameters?WT.mc_id=DP-MVP-5003068

Regards,

Tom

@BW40

[BW40]

@TomMartens Unfortunately, that was not what I needed. It looks like query parameters control what data is brought into the report but I need all of the data for various values on one report.

The values will interact with the same dates differently (some values need all dates up to the end date, other values will need everthing after a certain start date, then a third group of values will need if an end date ISBLANK as one of their conditions).

With two calculated columns (that I will have to adjust for users), I can get all of my values into three matrix tables. Using slicers it took 5 slicers, plus some filters for certain tables, to get it into 11 matrix tables.

It would be exponentially simpler if I could find a way to create two fields my users could adjust and then use those values as fields in all of my calculations. Is there a way to do this or will I need to wait for Power BI to catch up?

[TomMartens]

Hey @BW40 ,

you have to wait until Power BI catches up, but to be honest, I think this feature will never appear.

Regards,

Tom

[BW40]

@TomMartens thanks for the confirmation but that is dissappointing. 

I will chalk it up to a functional limitation in BI when they skipped that truely useful function. I made my report as simple as I could combining slicers and filters. I have to set so that it has 1 filter I change every year, 5 silcers I hope users don't miss (or they'll get bad data and never know), and six of my tables have specific fitlers I set (fortunately I only have to set those criteria filters once).

With the calculated columns, I was able to do it with DAX and only need to update two date calculated columns. I just need to have a way for users to change the date calculated columns themselves like Tableau is built to do wth its Parameters.