cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Power Participant

## TotalYTD formula resetting with start of new year

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:

Open Complaints =
TOTALYTD([Complaints#],
dimDate[Date],
'Case'[ClosedDate] > MAX(dimDate[Date])
)

Must be the reset for YTD going from 23 to 24 I gues??
Because also for 2nd column I made a TotalYTD without the filer for close date and there you see dec 23 on 748 and then it drops to 72 in the new year.

A proper YTD would keep counting would it not?
And I can only put 1 filter in cause I wanted to try adding extra filter ALL(dimDate[Date]) or something like that.

9 REPLIES 9
Power Participant

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??

Community Support

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.

Power Participant

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.

Power Participant

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.

Power Participant

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.

Better YTD =
VAR __Date = MAX(dimDate[Date])
VAR __Year = MAX(dimDate[Year])
VAR __Table = FILTER(ALL(dimDate),[Date] <= __Date && [Year] = __Year)
RETURN
SUMX('Case',COUNT('Case'[Id]))

So sure if it plays a role here but my connection with the autodate table has been setup like this:

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.

Super User

@rpinxt Can you post sample data as text?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Power Participant

@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:

Power Participant

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 😂

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).

Super User

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:

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors