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 Shares Count Current Year, Shares Count Previous Year. The Report date should be as on today (current date). 

 

Please suggest.

 

Input Table:

Companyshareid

ValidfromValidtoSharecount
s1015/23/2017 0:006/27/2018 0:005
s1022/17/2018 0:005/20/2018 0:0020
s1035/23/2017 0:006/27/2018 0:0010
s1045/20/2017 0:006/27/2018 0:0015
s1055/24/2017 0:001/30/2018 0:005
s1065/24/2016 0:005/24/2017 0:0016

 

Output Report:

Companyshareid 

Shares Count Current YearShares Count Previous YearDifference

 

Appreciate all the help!

 

Regards,

rnagalla25

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]

4 Replies

  • 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]