Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 

dates
DAX
IF ELSE
values
Visualization
  • ValtteriN's avatar
    ValtteriN
    4 years ago

    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!

  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

6 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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!

    • SteveFaber's avatar
      SteveFaber
      New Member
      1 year ago

      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?

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

     

     

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      4 years ago

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

      Measure1 = NOT ISEMPTY ( t1 )
      • smpa01's avatar
        smpa01
        Icon for Community Champion rankCommunity Champion
        4 years ago

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