YOY Line Chart - 2023 has broke my chart!

[Anonymous]

Hi, I'm at a massive loose end and would really appreciate if someone could try to figure out what is going on with my line chart. My YOY line chart worked perfectly up until the new year. I was showing a rolling value total over 1 1/2 months across the last 4 years which worked - image below.

But then ever since the 1st Jan 2023 the chart started looking like this: I hadn't changed the underlying data and was under the assumption I had a fully working chart.

This was the underlying data table with volume, values, dates, the data is grouped together in sql then fed into power bi. I created the DM column so I could do Many -> one between that and the calendar table

and this is the calendar table

Below is the running value total over time measure I created. I also tried a few other suggestions from youtube and did ALL('Calendar'), Calendar[Date} <= MAX (Calendar[Date]) but this was to no avail too.

This is the relationship I've selected. This all worked fine up until a few days ago.

What's also weird is when I unclick blank on the date filter (there isn't even a blank date, the date is the date from the calendar which I showed above) then only values for January appear. 

any help would be really great, I've been stuck on this issue for 4 hours and I've hit a brick wall. I've checked the underlying data and it's sound, could it be a setting or something in power bi that my administrator needs to fix? Open to any suggestions thanks!

 

 

[Ashish_Mathur]

Hi,

The relationship should be between Date and Date (not between DM and Date).  To your visual, drag DM and Year from the Calendar Table.  Write these measures

Total = sum(Data[value])

Total YTD = calculate([Total],datesytd(calendar[date]))

If this does not help, then share the download link of the PBI file.

[Anonymous]

Hi, I tried this but to no avail 😞 

Here is a link to the dummy data:

https://drive.google.com/file/d/1I9TspyAZQIFCUXQYCkeNl-xG6C6ZzznO/view?usp=sharing.

Please let me know if you can find a way around the problem.

many thanks

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hi Ashish,

Thanks, that kind of works but I was trying to show a line that goes from October - January +  across each year. In the chart I opened it starts at January. The issue I had was that it was working fine showing October - end of December, but when January came in the equation it couldn't display it. I've been tasked with reporting on Christmas activity which usually lasts from October - February so I need a cumulative line going through the new year if that makes sense!

I've found a temporary solution for now because I think this is just a limitation with Power BI or I just don't have the capacity to figure it out. I've amended all of the transactions to this year and then in the graph I've distinguished them with their campaign name. I've then changed the format of the receipt date so that it only shows the day and month. This works for now but if you have a way to do this without changing the underlying data that would be awesome!

[Ashish_Mathur]

Hi,

The max i can do is apply a filter as seen in the screenshot below.  But this too will only show data from October - December.

[Anonymous]

Hi Ashish,

Thank you. Would you say it is not possible to show October - 12th Jan in the chart? 

thanks

Simon 

[Ashish_Mathur]

I am just saying that i do not know how to do it.  May be someone else pitches in with a much better solution. 

[v-shex-msft]

Hi @Anonymous ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Anonymous]

Hi,

I've attached some dummy data below:

https://drive.google.com/file/d/1I9TspyAZQIFCUXQYCkeNl-xG6C6ZzznO/view?usp=sharing

I tried Ashish's suggestion but that didn't work.