Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
rpinxt
Power Participant
Power Participant

TotalYTD formula resetting with start of new year

Lets explain with this visual:

rpinxt_0-1709553788694.png

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
rpinxt
Power Participant
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??

v-tangjie-msft
Community Support
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.

 

vtangjiemsft_0-1709607966430.png

 

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 :

rpinxt_0-1709627520023.png

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
Power Participant
Power Participant

Maybe a new approach, taking in that New Complaints and Closed Complaints are correct:

rpinxt_0-1709565852278.png

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
Power Participant
Power Participant

Thanks @Greg_Deckler was able to get that into my example I included but giving me strange numbers :

rpinxt_0-1709561444650.png

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:
rpinxt_1-1709561679041.png

 

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? 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@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
Power Participant
Power Participant

Ok now I tried making a Ytd with calculate :

rpinxt_0-1709557196969.png

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:

rpinxt_1-1709557296855.png

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:



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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