Need help to calculate variation in different way

[Stormbreaker-SN]

Hello, I'd like some help calculating the variation in a particular way.
It's DayOverDayYear, the principle is that we need to be able to compare a day in a month in a year (01-01-2024) with the same day in the previous year (01-01-2023).
- I have a date table and it's well linked to the fact table.
- The comparison must include adjustments for public holidays, weekends, etc.
- I'm using a date table.

Here's an example of the result I want to achieve on a histogram

[v-linyulu-msft]

Thanks for the reply from moncx , please allow me to provide another insight:

Hi, @Stormbreaker-SN 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Next, use PowerQuery to create a custom index column as follows:

Date.Month([date])

3.Then sort the date column according to the index:
 

4.Then modify the visualization settings:

5.Apply the following measures to the filter:

Measure = 
IF(YEAR(MAX('main'[date]))>=YEAR(EDATE(TODAY(),-13)),1,0)

6.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stormbreaker-SN]

Hi @v-linyulu-msft , thank you very much for the solution you proposed.
I tried to reproduce the same thing taking into account my data, but I did not achieve the same result.
Your solution only works when the two years follow each other in the table.

01-01-2023

01-01-2024

And not when they don't: 

 
I'll give you more details that may help you understand my problem better. Here's an Excel file of some data from my Date table and my Fact table (I've only left two columns, but there are more).

I've attached the file below
Excel Files: Data file

[v-linyulu-msft]

Hi, @Stormbreaker-SN 

Thank you for the quick reply, I will answer your questions one by one:

First of all, there may be a misunderstanding between us, in my understanding the date format is mm-dd-yyyy, but according to your supplementary description, your date format is dd-mm-yyyy.
 

so in my previous reply it was sorted according to the month, and now you need to add the content of the custom column in power query to modify to:

Date.Day([date]) 

Secondly,due to our security practices, we do not have access to data sharing from Google.
 

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

If you have any other questions please feel free to contact me.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stormbreaker-SN]

Hello @v-linyulu-msft, thank you again 🙂,  
Yes, I understand what you mean, and I've followed your solution step by step and even applied the month filter to the date,
I have this :  

but it's not a date format problem I've noticed that if the dates follow each other, as in your example, there's no problem displaying the days side by side, but as soon as they don't follow each other, there's a problem. I've tried filtering by index, but it doesn't work, perhaps when you see the file I'm going to attach to OneDrive, you'll be able to test and understand what I mean especially since I have a date table and a fact table, the date table handles time intelligence.

I tried to put it on OneDrive as suggested in the forum.
File: Datas.xlsx

[moncx]

If I understood your problem correctly - you can write a measure using SAMEPERIODLASTYEAR function in DAX, it would look something like this:
Measure SPLY = CALCULATE([Your original Measure],SAMEPERIODLASTYEAR(DateTime[DateKey]))

Variation volume can be calculated depending on what do you consider it, if it is difference between current and last year then just create additional measure for that.

To filter out public holidays, weekends add aditional columns to date table and filter them out later in visual (for weekend you can add aditional collumn with name of weekday: Day of week name = FORMAT ( 'date'[Date], "dddd" ); and for public holidays you have to have a lost depending on your country).

In this situation it probably is best to select line and cluster column chart in Power BI, put Day from date table hierarchy into X-axis, put measures [Your original Measure] and [Measure SPLY] into column Y-axis and then [Measure DIFF] into line y-axis.

[Stormbreaker-SN]

Hello @moncx  , thank you very much for your approach. I followed your logic which I find good just that I do not have the exact result as in the image I submitted having on the date on the y axis. here is the result:

I want it to display, for example, 01-08-2023 and 01-08-2024.
I've tried creating a column YearMonthDay = FORMAT([Date], "YYYY-MM-DD”) And creating a filter column on the month I'm going to apply to YearMonthDay, but I get this result:

While I wanted it side by side like 01-08-2023 instead of 01-08-2024.

Thanks in advance

[v-linyulu-msft · Accepted solution]

Hi, @Stormbreaker-SN 

Thanks for the quick reply.

Regarding the issue you raised, my solution is as follows:

1.First, you can add the following custom columns in PowerQuery:

Date.ToText([Date], "dd") & Date.ToText([Date], "MM") & Date.ToText([Date], "yyyy")

2.Secondly, sort the dates in the desktop as shown in the figure:

3.Next, modify the visualization settings:

4.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Stormbreaker-SN]

Hello @v-linyulu-msft, thank you very much for your solution, I've tried it and it works :

[moncx]

First visual looks good, but if you need to see full date for each colum (and not year on legend and month+day on the bottom) then maybe you could try to create custom data label and put it on the columns? Here is a good example how to create custom data labels: How CUSTOM LABELS in Power BI Will Make Your REPORTS Shine (youtube.com) (you could even use measure with the YearMonthDay (that you written before) logic.

[Stormbreaker-SN]

Hi @moncx, thank you very much for the link, it's a great help to customize the display of the labels I used it to set the volume and variation at the same time