Forum Discussion
Request for DAX Help
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]
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?
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))
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]
Hi Phil Seamark,
Thank you so much for the response, I now able to generate the report.
Appreciate all your help.
Regrads,
rnagalla