Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Request for DAX Help

Dear All,  I request help to solve a scenario.  I have a input table, using that I need to create a output report (Refer tables below). I am having trouble in creating the dax formulas for columns S...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi Anonymous

     

    I think I see what you are after.  Please try the following three calculated measures.  I have attached a simple PBIX file that contains the measures.

     

    Shares Count Current Year = 
VAR myDate = TODAY()
RETURN 
    SUMX(
        FILTER(
            'Table1',
            'Table1'[Validfrom] <= myDate &&   
            'Table1'[Validto] >= myDate
        ),
       'Table1'[Sharecount]
       )
    Shares Count Previous Year = 
VAR myDate = EDATE(TODAY(),-12)
RETURN 
     SUMX(
        FILTER(
            'Table1',
            'Table1'[Validfrom] <= myDate &&   
            'Table1'[Validto] >= myDate
        ),
       'Table1'[Sharecount]
       )
    Difference = [Shares Count Current Year] - [Shares Count Previous Year]

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

What are the criteria for counting if a row should be considered as this year?  Does it have to start in this year or just be active at any point during the year?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Below is the logic they have given in the storeprocedure

    @Date = getdate()

    @FromDate = DATEADD(Year, -1, @Date)

    ISNULL(dbo.GetShares(c.CompanyID, @Date),0) AS Shares count current year
    ,ISNULL(dbo.GetShares(c.CompanyID, @FromDate),0) AS Shares count Previous Year

     

    GetShares Function is having below logic 

     

    CONVERT(datetime,CONVERT(char(10),@Date,101)) BETWEEN CONVERT(datetime,CONVERT(char(10),ValidFrom,101)) AND CONVERT(datetime,CONVERT(char(10),ValidTo,101)) 

     

     

     

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      Hi Anonymous

       

      I think I see what you are after.  Please try the following three calculated measures.  I have attached a simple PBIX file that contains the measures.

       

      Shares Count Current Year = 
VAR myDate = TODAY()
RETURN 
    SUMX(
        FILTER(
            'Table1',
            'Table1'[Validfrom] <= myDate &&   
            'Table1'[Validto] >= myDate
        ),
       'Table1'[Sharecount]
       )
      Shares Count Previous Year = 
VAR myDate = EDATE(TODAY(),-12)
RETURN 
     SUMX(
        FILTER(
            'Table1',
            'Table1'[Validfrom] <= myDate &&   
            'Table1'[Validto] >= myDate
        ),
       'Table1'[Sharecount]
       )
      Difference = [Shares Count Current Year] - [Shares Count Previous Year]

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Phil Seamark,

         

        Appreciate all your help.

         

        Regrads,

        rnagalla