Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I'm experiencing a weird behaviour using a cumulative total wiht the following formula:
Cumulative_actual_1 = CALCULATE ( DISTINCTCOUNT ( 'ChangeStatus (2)'[issueid] ); FILTER ( ALLEXCEPT ( 'ChangeStatus (2)'; 'ChangeStatus (2)'[Status]); 'ChangeStatus (2)'[Month] <= MAX ( 'ChangeStatus (2)'[Month] ) ) )
The problem is when I display the formula in a column chart:
As you can see I have 613 on the chart but in fact the total is 219, the chart is filter to only display the year 2017 and the AllEXcept is to ensure I can show the count by status.
How can I solve this?
Thanks
Solved! Go to Solution.
Hi,
See my solution here.
Hope this helps.
Hi @Anonymous,
Here's what i would do:
=CALCULATE(DISTINCTCOUNT('ChangeStatus (2)'[issueid]),DATESYTD(Calendar[Date],"31/12"))
Hope this helps.
Thanks for the reply. Your aproach in fact did not work because I have complete dates but the calendar gives "only" the days.. so the relation does not work.
Thanks for the reply, but it gave me even higher values... 😞 I was using a measure, but in your solution I had to create a column to work it Earlier.
this seems a simple calculation.. but.. maybe my data model is not standard.
Hi @Anonymous,
Share the link from where i can download your workbook.
Let me work on one to be able to send you. The one I have I can not send it.
I will post the link after that.
Thanks
Hi,
See my solution here.
Hope this helps.
Thanks @Ashish_Mathur I used your solution and manage to make it work on my pbix.
The problem is that created was a datetime type, but in order to work I had to change it for a Date type. I think this happens because calendar uses Date and not datetime as the date field.
You are welcome.
@Anonymous,
Please try to use the DAX below.
Cumulative_actual_1 = CALCULATE ( DISTINCTCOUNT ( 'ChangeStatus (2)'[issueid] ); FILTER ( ALLEXCEPT ( 'ChangeStatus (2)'; 'ChangeStatus (2)'[Status]); 'ChangeStatus (2)'[Month] <= EARLIER( 'ChangeStatus (2)'[Month] ) ) )
Regards,
Charlie Liao
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |