The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
136 | |
101 | |
101 | |
72 | |
58 |
User | Count |
---|---|
263 | |
121 | |
114 | |
93 | |
85 |