topic Re: IF value exists on calendar date in DAX Commands and Tips

IF value exists on calendar date

Anonymous — Mon, 03 Jan 2022 16:00:58 GMT

Hi everyone, i need help creating a measure that evaluates whether a value in on table exists at a particular date within the calender table .

I am working with Two tables, on main table that has a customer and a purchase date. The second table is just a table showing the daily calendar dates.

the outcome wanted is to evaluate whether a purchase was made by a customer at a certain date on the daily calender.

the tables are as shown below :

RESULT

If a customer has purchased something on the date equivalent to the calendar date, return true, else false. as shown below:

If anyone could assist me with writing a measure that could produce this outcome that would be wonderful 🙂 all help and sugegestions are welcome:

Thank you

Re: IF value exists on calendar date

ValtteriN — Mon, 03 Jan 2022 16:30:01 GMT

Hi,

Something like this should do what you want:

Purchased = IF(CALCULATE(COUNTROWS(PurchaseBoolean),SELECTEDVALUE('Calendar'[Date])='PurchaseBoolean'[Date])>0,TRUE(),FALSE())

Start data:

End result (Use calendar for axis):

(Ignore the Matt Matt. I made a typo in my test data 😅)

I hope this helps and if it does consider accepting this as a solution and giving a thumbs up!

Re: IF value exists on calendar date

smpa01 — Mon, 03 Jan 2022 16:30:44 GMT

Anonymous you can use a measure like this

Measure = VAR _cust = MAX ( t1[customer] ) VAR _date = MAX ( 'Calendar'[Date] ) VAR _temp = CROSSJOIN ( SELECTCOLUMNS ( { _cust }, "cust", [Value] ), SELECTCOLUMNS ( { _date }, "dt", [Value] ) ) VAR _date2 = CALCULATE ( MAX ( t1[purchse_date] ), TREATAS ( _temp, t1[customer], t1[purchse_date] ) ) RETURN IF ( _date2 = BLANK (), "false", "true" )

Re: IF value exists on calendar date

AlexisOlson — Mon, 03 Jan 2022 17:21:16 GMT

If the calendar table is related to the purchase date, then all you need is

Measure1 = NOT ISEMPTY ( t1 )

Re: IF value exists on calendar date

smpa01 — Mon, 03 Jan 2022 17:26:03 GMT

AlexisOlson OP did not mention anyhting about the data model, so my starting point was unrelated tables. Thanks again !!!

Re: IF value exists on calendar date

AlexisOlson — Mon, 03 Jan 2022 17:38:29 GMT

Yeah, without the relationship, it needs a TREATAS or similar application of the date filter.

CALCULATE ( NOT ISEMPTY ( t1 ), TREATAS ( VALUES ( 'Calendar'[Date] ), t1[purchse_date] ) )

Re: IF value exists on calendar date

SteveFaber — Tue, 28 Jan 2025 09:46:30 GMT

I have a slightly different problem with these dates:

I have a percentage table that for existing dates should return 1-tx and not show all other dates

Now, as for each occurence where tx does not exist, 1-tx gives 100%, even when showing only Q1,
I get results per week 1-52 with all weeks outside Q1 showing 100%...

How can I force PBI to only show weeks within the selected filter?