Forum Discussion

Giavo's avatar
Giavo
Helper III
7 years ago
Solved

DAX: SELECTEDVALUE with Dates

Hello all,

 

INTRO:

i have a Relational model with 3 tables:

1)Project Milestones table containing Project Codes and 4 dates per each project (Date1, Date2, Date3, Date4)
2)Projects Table containing unique project codes

3)Locations table containing Locations and related Project codes

 

I have a rule in Project Milestones table in order to determine if a project is ACTIVE in a certain year and it is as follow:

Column:

Active in 2018 = IF((YEAR(Date 1)<=2018 || YEAR(Date 2)<=2018) &&
                                 (YEAR(Date 3)>= 2018 || YEAR(Date 4)>=2018)."Active"."Not Active")

 

This rule gives me all the ACTIVE Projects in 2018 displayed per Location.
PROBLEM:
I want the user to be able to select a YEAR himself in order to see all the active projects for the selected year; (not fixed 2018 anymore)

i tried this:
i have created a new Calendar table containing dates and applied this rule:

Active in X =
VAR SelectedYear=SELECTEDVALUE('Calendar'[Year])
RETURN 
IF((YEAR(Date 1)<=SelectedYear || YEAR(Date 2)<=SelectedYear) &&
                                 (YEAR(Date 3)>= SelectedYear || YEAR(Date 4)>=SelectedYear)."Active"."Not Active")

Apparently SelectedYear=SELECTEDVALUE('Calendar'[Year]) doesn't work properly. Do you know how can i improve this rule ?

Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Giavo I am just looking for sample data not actual data, but anyway I guess you are looking for this..

     

    Test122Count = 
    VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
    VAR _Temp = FILTER(Test122,
     ((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
    VAR _Temp1 = IF(COUNTROWS(_Temp)>0,1,0)
    RETURN SUMX(_Temp,_Temp1)

  • richbenmintz's avatar
    richbenmintz
    7 years ago

    Hi Giavo,

     

    I think the following formula should do what you expect, essentially it will count the rows on the 'Project Milestones' table where the conditions are met based on the selected value

     

    Active in X =
VAR SelectedYear=SELECTEDVALUE('Calendar'[Year])
RETURN 
Calculate(CountRows('Project Milestones'), FILTER('Project Milestones', (YEAR(Date 1)<=SelectedYear || YEAR(Date 2)<=SelectedYear) &&
(YEAR(Date 3)>= SelectedYear || YEAR(Date 4)>=SelectedYear)))

12 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Giavo

    As tested, PattemManohar's solution is helpful, could you check it on your site, if you have problem please don't hesitate to ask.

     

    Best Regards

    Maggie

     

      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        7 years ago

        Hi Giavo,

         

        I think the following formula should do what you expect, essentially it will count the rows on the 'Project Milestones' table where the conditions are met based on the selected value

         

        Active in X =
VAR SelectedYear=SELECTEDVALUE('Calendar'[Year])
RETURN 
Calculate(CountRows('Project Milestones'), FILTER('Project Milestones', (YEAR(Date 1)<=SelectedYear || YEAR(Date 2)<=SelectedYear) &&
(YEAR(Date 3)>= SelectedYear || YEAR(Date 4)>=SelectedYear)))
  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Giavo Please try this as a "New Measure"

     

    Sample Data:

     

     

    Measure:

     

    Test122 = 
VAR _SelectedYear = SELECTEDVALUE(_DimDate[Year])
VAR _Temp = FILTER(
                                Test122,
                                ((YEAR(Test122[Date1])<=_SelectedYear || YEAR(Test122[Date2])<=_SelectedYear) && (YEAR(Test122[Date3])>=_SelectedYear || YEAR(Test122[Date4])>=_SelectedYear)))
RETURN IF(COUNTROWS(_Temp)>0,"Active","NotActive")

    • Giavo's avatar
      Giavo
      Helper III
      7 years ago

      Thank you for your help, it is useful but howewer it's not working 100%

      If i put 2 columns in a table i have this result,
      Project,          Active
      Project1      Not Active
      Project2      Active
      Project3      Active

      So apparently it works. But when i want to count per each year the number of Active Project it doesn't work anymore and i see this result :
      Year , Count Projects

      1999      199

      2000      199

      2001      199

      2002      199

      2003      199

      I'm wondernig why ? Per each project it tells if the project is Active or not, but if i count the number of projets per year it doesn't work anymore

      • PattemManohar's avatar
        PattemManohar
        Community Champion
        7 years ago
        Giavo Could you please post the sample data and expected output, which will help us to understand the scenario in more detail.