cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## IF value exists on calendar date

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

2 ACCEPTED SOLUTIONS
Super User

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!

Proud to be a Super User!

Super User

@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" )
``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
5 REPLIES 5
Super User

@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" )
``````

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

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

``Measure1 = NOT ISEMPTY ( t1 )``
Super User

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

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Super User

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] )
)``````
Super User

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!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors