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.
I have already done a normal cumulative report with this DAX
Cumulative Licwnse = CALCULATE (COUNTA ( 'Cumulative'[Username] ),
FILTER (ALL ( 'Calendar'[Date] ),'Calendar'[Date] <= MAX ( 'Calendar'[Date] )))
Let get started on my logic.
Now, my new requirement is to add the new condition to update them. By the way, my logic is when I put the data in delete field that means this user is no longer anymore so next year the graph will be decreased. But if I don't put any data in the delete column that means this user continues using next year.
I have this sample data.
Username | Creation date | Delete | Depart. |
as01 | 12/9/2016 | Accountiant | |
as02 | 12/9/2016 | 13/9/2017 | Accountiant |
as03 | 12/6/2017 | Accountiant | |
cs01 | 12/6/2017 | 13/9/2018 | Marketing |
CS02 | 13/6/2017 | Marketing |
The result from my logic is following below sample chart.
Year | 2016 | 2017 | 2018 |
Username | 2 | 5 | 4 |
Could you please give me a DAX solution?
Thank you all for answering.
Solved! Go to Solution.
Hi @saranp780,
You need to have in your calculation a reference to the time frame and not only to the start time of the license.
Assuming that your calendar table is not related with your cumulative table.
Use the following measure:
Cumulative License = VAR Selected_year = YEAR ( MAX ( 'Calendar'[Date] ) ) RETURN CALCULATE ( COUNTA ( 'Cumulative'[Username] ); FILTER ( ALL ( CUMULATIVE[Creation date ] ); YEAR ( CUMULATIVE[Creation date ] ) <= Selected_year ); FILTER ( ALL ( CUMULATIVE[Delete] ); YEAR ( CUMULATIVE[Delete] ) >= Selected_year || CUMULATIVE[Delete] = BLANK () ) )
See result below:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @saranp780,
You need to have in your calculation a reference to the time frame and not only to the start time of the license.
Assuming that your calendar table is not related with your cumulative table.
Use the following measure:
Cumulative License = VAR Selected_year = YEAR ( MAX ( 'Calendar'[Date] ) ) RETURN CALCULATE ( COUNTA ( 'Cumulative'[Username] ); FILTER ( ALL ( CUMULATIVE[Creation date ] ); YEAR ( CUMULATIVE[Creation date ] ) <= Selected_year ); FILTER ( ALL ( CUMULATIVE[Delete] ); YEAR ( CUMULATIVE[Delete] ) >= Selected_year || CUMULATIVE[Delete] = BLANK () ) )
See result below:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |