Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Lets explain with this visual:
Lets take Nov 23 to start. We see we have 50 (new) complaints and 60 closed complaints.
This means 10 more where close than that were openend so the Open complaints should drop from 29 to 19.
And that is what it does.
Next Dec 23. 67 new, 49 closed so +18 for the open. 19 + 18 = 37, so that is ok too.
But then we go to the new month. 72 new, 82 closed. Should be -10. But we get minus 11
Feb 24 even worse. 74 new, closed 77. We expect a minus 3 and get a minus 20??
This is the formula for Open Complaints:
Maybe this is harder than I expected....
Would there not be a formula that sums als (Open Complaints -/- Closed Complaints) for all dates?
So if I put a filter to see only last 13 months it still will sum for all periods??
Hi @rpinxt ,
We can create measures.
Measure 2 = IF( OR(MAX('dimDate'[Date])=BLANK(),YEAR(MAX('dimDate'[Date]))=2023 && MONTH(MAX('dimDate'[Date]))=2),0,[New Complaints]-[Closed Complaints])
Measure 3 = IF(MAX(dimDate[Date])=BLANK(),BLANK(), 20+ SUMX(FILTER(ALLSELECTED(dimDate),[Date]<=MAX('dimDate'[Date])),[Measure 2]))
If this doesn't help you, please point out the logic behind the value of 20 for 02-2023.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @v-tangjie-msft !
This looks to be doing what I was expecting.
But I do have some concerns about the hardcode values in the measure.
Like 2023. It is a an ongoing time lines not only 2 years of data.
There is a filter on to show only the last 13 months :
So that is also why you see the strange thing in 02-2023.
But the measure should be working thru all periods/years.
I only just wanted to see the last 13 month but those will shift of course.
So if you then hardcode 2023 or month 02 it will not work I guess.
Maybe a new approach, taking in that New Complaints and Closed Complaints are correct:
Then subtracting New -/- Closed at least gives me the expected amounts per period.
However it would need to aggregate everything, the period and all periods before it.
Would there be a Dax formula to do this? Simple Sum will not work as it is no field but a measure.
Thanks @Greg_Deckler was able to get that into my example I included but giving me strange numbers :
These numbers are way to high.
This is how I converted your dax to my fields.
As said I don't think it has an effect on the dax code but it is an extra 'problem' getting correct, New/Closed and Open complaints numbers.
@rpinxt Can you post sample data as text?
@Greg_Deckler don't think that will work as it is a colletion of lots of underlying lines. What you see in the visual is only an aggregation.
Unfortunately I could not upload my example pbix file here but I did put it here in the link:
https://drive.google.com/file/d/1srVlfP2CSSqoE8R47sIVQ2ZdM2U4oDfR/view?usp=drivesdk
Ok now I tried making a Ytd with calculate :
Where I thought Nov 23 was at least still ok, now the calculate function comes with 18 instead of 19....
So looking in the details:
The yellow lines are the lines where the close date was after the period. And if you count these they indeed come to 18 !
So now I am doubting everything 😂
I made a test file and uploaded it to drive.
Maybe this helps more to understand.
What I want is not to complicated on paper.
I want to see by periode the new complaints, the closed complaints (in that period) and the still open complaints (in that period).
Link :
https://drive.google.com/file/d/1srVlfP2CSSqoE8R47sIVQ2ZdM2U4oDfR/view?usp=drivesdk
please let me know if the link does not work!
@rpinxt Try this: Better Year to Date Total - Microsoft Fabric Community
You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Also:
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
93 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
128 | |
102 | |
98 |