Year to Date Function

[mtrussardi]

Hi,

I have table that shows sales for 3 different years (2018,2019,2020), and I would like to compare the difference in percentage of the sales year to date . For example: Sales accumulated from January 2020 until October 2020 and sales from January 2019 and October 2019. Is there any way I can compute a function that calculates this percentage automatically based on the same amount of months each year.

Here is what I am looking for: 

 

[themistoklis · Accepted solution]

@mtrussardi 

Create 2 measures for YTD and LYTD and then calculate the percentage diff:

YTD = CALCULATE(SUM(Table6[Amount]),DATESYTD('Calendar'[Date]))

YTD LY = CALCULATE(Table6[YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

Measue = ([YTD] - [YTD LY]) / [YTD LY]

[themistoklis · Accepted solution]

@mtrussardi 

Create 2 measures for YTD and LYTD and then calculate the percentage diff:

YTD = CALCULATE(SUM(Table6[Amount]),DATESYTD('Calendar'[Date]))

YTD LY = CALCULATE(Table6[YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

Measue = ([YTD] - [YTD LY]) / [YTD LY]

[mtrussardi]

@themistoklis 

Thank you for your reply.

I have created the 2 measures and the column with percentage diff calculation. However, it returns an infinte sign. If it helps here a sample the table.

[themistoklis]

@mtrussardi 

Could you send a snapshot of the table on PowerBI where the infinite sign appears?

The table that you sent has only 2018 data. Could you share the PBIX file and mask any confidential data?

[mtrussardi]

This is the table that I am working on right now. Please, if you need the file let me know and I will dm you. Thanks in advance for your time.

[themistoklis · Accepted solution]

@mtrussardi 

It seems that the Change % is a column and not a measure.

You should create a measure.

Also the table that you are showing us contains the raw data table.

What is the actual table that you are planning to use on the report. What fields and measures is it going to have?

[mtrussardi]

@themistoklisThank you so much, now it is working