Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How we can user user input parameter in a column with DAX formula to change a date

Hello Users, I want to use user input parameter to calculate a past date from a date column which is already present in cloud dataset.

for example: user select 4 as a integer from user input slicer and if the cal_date column in dataset has date 23rd June 2022 then in calculated column the date should be 23rd Feb 2022. ie the calculated column should change the date by no of months selected by user.

 

i am using below formula in calculated column to get past date :- 

EDATE(CD[Cal_Date],(-1*5))
Negative number as we want a past date.
CD[Cal_Date] is a calculated date column from day, month & year column by concatenating.
 
This is what I want to replicate from a tableau dashboard.
but what I see in power BI user input parameter is only used in measure not in calculated column that's what I know might be wrong.
but I didn't find any dax formula in a measure to calculate date.
 
Please guide me.
Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous,

    Current power bi does not support creating dynamic calculated column/table based on filter selections. I'd like to suggest you create a measure to interact with the 'what if' parameter slicer.

    SELECTEDVALUE function - DAX | Microsoft Docs

    Notice: the data level of power bi.

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your reply.

    but we cant create measure for date field right..?

    i tried it but date is not possible in measure.

    correct me if I am wrong.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HI Anonymous,

      You can try to create a query parameter and turn on the 'enable load' option on the query editor side.

      Deep Dive into Query Parameters and Power BI Templates | Microsoft Power BI Blog | Microsoft Power BI
      This parameter table will be loaded into the data model, then you can use your formula to extract this table values to calculate with your expression.
      After these steps, you can modify the parameter and refresh the data model table to update the calculated column result based on the parameter table values.

      Regards,

      Xiaoxin Sheng