TotalYTD formula resetting with start of new year

[rpinxt]

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.

[rpinxt]

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

[v-tangjie-msft]

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. 

[rpinxt]

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.

[rpinxt]

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.

[rpinxt]

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.

[Greg_Deckler]

@rpinxt Can you post sample data as text? 

[rpinxt]

@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

[rpinxt]

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!

[Greg_Deckler]

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