Line graph automatically displays total for entire last year's month

[Anonymous]

The issue revolves around a line graph designed for comparison, specifically depicting total sales for the current month up to the current date compared to the same period last year. The aim is to provide stakeholders with an accurate representation of performance for this year compared to last year. 

The challenge arises when all months are selected on the graph (by default) whereby the data is visualized by year. In this scenario, the graph erroneously totals the sales value for November 2022, while the data for November 2023 correctly extends only to the current date (16th November). This inconsistency in visualization has the potential to mislead stakeholders about the actual performance.

Im looking for a solution to refine the DAX formula or implement a solution that guarantees the visualization for last year's data accurately represents sales figures up until the date of last year corresponding to today's date (current date). Would appreciate any and all input as i'm a bit stumped on this one. 

[Anonymous]

Try the following:

Total Sales = SUM('YourTable'[TotalSales])

Total Sales LY = CALCULATE([Total Sales], FILTER(ALL('YourTable'),

                                                     YEAR('YourTable'[Date]) = YEAR(SELECTEDVALUE('YourTable'[Date])) - 1

                                                     && MONTH('YourTable'[Date]) = MONTH(SELECTEDVALUE('YourTable'[Date]))

                                                     && DAY('YourTable'[Date]) = DAY(SELECTEDVALUE('YourTable'[Date])))

)

This should only return records that are a match on the month and the day of the month from one year ago. Hope this helps, let me know if you have any issues or further questions.

[Anonymous]

Thanks for your answer and for taking the time Toby. Greatly appreciated. However, when I plot this in with the tables, the values just dissappear. Could it have something to do with the formatting of "Date"? In my table, "Date" is formatted by: *DDMMYYYY (i.e. short date). Data type is date. 

I have the following columns as well (using example 01-07-2020 for format): 

• Date: 01-07-2020
• Year: 2020
• Monthnumber: 07
• YearMonth: YYYY-M07
• YearMonthShort: 2020-jul
• MonthShort: jul
• Week_ISO: 27
• year-ISO: 2020
• YearWeek: 2020-W27
• DayOfWeekNumber: 4
• DayOfWeek: wednesday
• DayOfWeekShort: wed
• Quarter: /Q3
• YearQuarter: 2020-Q3
• Month Name: juli
• DayOfWeekNumText: 3. Onsdag
• IsBeforeToday: True

Is there something that i'm missing here? 

[Anonymous]

I don't think so, though if you already have all of those columns in your calendar table you can just use those instead of YEAR('YourTable'[Date]) and all the rest of them. If you could send me a picture of your visual so I can see which fields are used in it and where they come from that would help, I've also re-written the measure slightly to tackle an issue I think it may be:

Total Sales = SUM('SalesTable'[TotalSales])

Total Sales LY = CALCULATE([Total Sales], FILTER(ALL('SalesTable'),

                                        YEAR('SalesTable'[Date]) = YEAR(SELECTEDVALUE('Calendar'[Date])) - 1

                                        && MONTH('SalesTable'[Date]) = MONTH(SELECTEDVALUE('Calendar'[Date]))

                                        && DAY('SalesTable'[Date]) = DAY(SELECTEDVALUE('Calendar'[Date])))

)

If the above doesn't work or that is already how you were doing it then send me which fields are in the visual. Effectively you want to filter the sales table date field where your sales value lies with the selected value of the date field in the visual that you are using. Hope this helps, let me know how you get on.