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