Request for DAX Help

[Anonymous]

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	Validfrom	Validto	Sharecount
s101	5/23/2017 0:00	6/27/2018 0:00	5
s102	2/17/2018 0:00	5/20/2018 0:00	20
s103	5/23/2017 0:00	6/27/2018 0:00	10
s104	5/20/2017 0:00	6/27/2018 0:00	15
s105	5/24/2017 0:00	1/30/2018 0:00	5
s106	5/24/2016 0:00	5/24/2017 0:00	16

Output Report:

Companyshareid

Shares Count Current Year

Shares Count Previous Year

Difference

Appreciate all the help!

Regards,

rnagalla25

[Phil_Seamark]

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]

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 · Accepted solution]

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]

Hi Phil Seamark,

Thank you so much for the response, I now able to generate the report.

Appreciate all your help.

Regrads,

rnagalla