Parameter in Calculated column

[Anonymous]

Hi,

I have an excel formula that uses two parameter dates that are inputted by the user in my spreadsheet.

E.g. IF(AND(ParameterStartDate<[@[Start Date]],ParameterEndDate>[@[End Date]]). 

What would be the best way to do this type of date formula on Power BI at row level, but so the user can use a slicer on the report page instead of a parameter?

Thanks

Liam

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

A measure will reflect the selection of the slicer in time, and the column is refreshed only after loading and clicking the Refresh button, and can't be interacted with other visual. I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You create a meaure like below.

Result = 
var _parameterstartdate = 
CALCULATE(
    MIN('Calendar'[Date]),
    ALLSELECTED('Calendar')
)
var _parameterenddate = 
CALCULATE(
    MAX('Calendar'[Date]),
    ALLSELECTED('Calendar')
)
return
IF(
    _parameterstartdate<SELECTEDVALUE('Table'[StartDate])&&
    _parameterenddate>SELECTEDVALUE('Table'[EndDate]),
    "Yes","No"
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft ,

Thats great, thankyou so much for explaining.

The pbix file says Object reference not set to an instance of an object. Would this be something to do with the version?

Thanks

Liam

[v-alq-msft]

Hi, @Anonymous 

You may try to update Power BI  Desktop to the latest version to see if it helps. The problem may also relate to the account. You may refer to object reference not set to an instance of an object while signing in .

Best Regards

Allan

[amitchandak]

@Anonymous , Where do you need that.

In measure

Grand Total Orders for Selected Date Range = 
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(allselected(Date),Date[Date])
return 
Calculate([Measure],filter(ALL(Date),Date[Date]<_min && PerformanceTable[Date]>_max))

Use a date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Anonymous]

Hi @amitchandak ,

Would this work in a calculated column also?

Thanks

Liam

[amitchandak]

@Anonymous , You can not pass the slicer parameter to calculated columns. So think the term of measure only