Forum Discussion

Giavo's avatar
Giavo
Icon for Helper III rankHelper 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 cont...
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)))
PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity 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
    Icon for Helper III rankHelper 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
      Icon for Community Champion rankCommunity 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.
      • Giavo's avatar
        Giavo
        Icon for Helper III rankHelper III
        7 years ago

        Unfortunately i can't share the dataset, But basically i would like to count per each year the number of Active Projects so:

        Year   Count Active Projets
        1999          21

        2000          39

        2001         10

        2002          2