Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |