Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
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 :

Screen Shot 2022-01-03 at 9.38.39 AM.pngScreen Shot 2022-01-03 at 9.49.36 AM.png

 

 

RESULT

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

 

Screen Shot 2022-01-03 at 9.36.37 AM.png

 

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
ValtteriN
Super User
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:
ValtteriN_0-1641227243189.png


End result (Use calendar for axis):

ValtteriN_1-1641227259899.png
(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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

smpa01
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" )

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

5 REPLIES 5
smpa01
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" )

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

Measure1 = NOT ISEMPTY ( t1 )

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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] )
)
ValtteriN
Super User
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:
ValtteriN_0-1641227243189.png


End result (Use calendar for axis):

ValtteriN_1-1641227259899.png
(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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors