YTD

[kalkhudary]

Good morning 

I am trying to calculate the % change from a selected date (ie March 2021) on a visual from original start date (i.e. Jan 2017). Not sure how to create the dax having a dataset that includes date range from Jan 2017 till Oct 2022 and 3 types of residentials ( house, condo, land) with City names. Trying to create something like this metric board.

[kalkhudary]

@Anonymous I am attaching the sample data for one city for your reference. Hope this helps.

Date	Geography	New Housing & Residential Type	Price Index Value
2017-01	Calgary, Alberta	House only	99.8
2017-01	Calgary, Alberta	Land only	100
2017-01	Calgary, Alberta	Condo Only	100.9
2017-04	Calgary, Alberta	House only	99.8
2017-04	Calgary, Alberta	Land only	100
2017-04	Calgary, Alberta	Condo Only	102.4
2017-07	Calgary, Alberta	House only	100.2
2017-07	Calgary, Alberta	Land only	100.3
2017-07	Calgary, Alberta	Condo Only	99.5
2017-10	Calgary, Alberta	House only	100.2
2017-10	Calgary, Alberta	Land only	100.5
2017-10	Calgary, Alberta	Condo Only	97.1
2018-01	Calgary, Alberta	House only	100.1
2018-01	Calgary, Alberta	Land only	100.7
2018-01	Calgary, Alberta	Condo Only	95.9
2018-04	Calgary, Alberta	House only	98.9
2018-04	Calgary, Alberta	Land only	100.6
2018-04	Calgary, Alberta	Condo Only	92.3
2018-07	Calgary, Alberta	House only	99.5
2018-07	Calgary, Alberta	Land only	100.8
2018-07	Calgary, Alberta	Condo Only	92.6
2018-10	Calgary, Alberta	House only	98.3
2018-10	Calgary, Alberta	Land only	100.4
2018-10	Calgary, Alberta	Condo Only	89
2019-01	Calgary, Alberta	House only	98.6
2019-01	Calgary, Alberta	Land only	100.2
2019-01	Calgary, Alberta	Condo Only	86
2019-04	Calgary, Alberta	House only	97.5
2019-04	Calgary, Alberta	Land only	100.2
2019-04	Calgary, Alberta	Condo Only	84.4
2019-07	Calgary, Alberta	House only	95.8
2019-07	Calgary, Alberta	Land only	100.1
2019-07	Calgary, Alberta	Condo Only	82.9
2019-10	Calgary, Alberta	House only	95.3
2019-10	Calgary, Alberta	Land only	99.9
2019-10	Calgary, Alberta	Condo Only	79.8
2020-01	Calgary, Alberta	House only	95
2020-01	Calgary, Alberta	Land only	100.1
2020-01	Calgary, Alberta	Condo Only	78
2020-04	Calgary, Alberta	House only	94.5
2020-04	Calgary, Alberta	Land only	99.7
2020-04	Calgary, Alberta	Condo Only	78
2020-07	Calgary, Alberta	House only	93.5
2020-07	Calgary, Alberta	Land only	99.7
2020-07	Calgary, Alberta	Condo Only	77.5
2020-10	Calgary, Alberta	House only	94.4
2020-10	Calgary, Alberta	Land only	100.3
2020-10	Calgary, Alberta	Condo Only	78.6
2021-01	Calgary, Alberta	House only	95.5
2021-01	Calgary, Alberta	Land only	100.1
2021-01	Calgary, Alberta	Condo Only	78.7
2021-04	Calgary, Alberta	House only	101.6
2021-04	Calgary, Alberta	Land only	100.1
2021-04	Calgary, Alberta	Condo Only	79.4
2021-07	Calgary, Alberta	House only	110.8
2021-07	Calgary, Alberta	Land only	103.4
2021-07	Calgary, Alberta	Condo Only	80.5
2021-10	Calgary, Alberta	House only	111.6
2021-10	Calgary, Alberta	Land only	103.2
2021-10	Calgary, Alberta	Condo Only	80.9
2022-01	Calgary, Alberta	House only	116.1
2022-01	Calgary, Alberta	Land only	104.3
2022-01	Calgary, Alberta	Condo Only	80.9
2022-04	Calgary, Alberta	House only	131.1
2022-04	Calgary, Alberta	Land only	107.3
2022-04	Calgary, Alberta	Condo Only	83.4

[Anonymous]

Hi @kalkhudary ,

You can refer the following links to get YTD and the percentage:

YTD:

How to Create YTD Calculations in Power BI​

Power BI Tips: Calculate Year-to-date Values

Sales YTD = TOTALYTD ( SUM ( Orders[Sales] ), Orders[Order_Date], ALL ( Orders ) )

Percentage:

Year To Date percentage

YTD% = CALCULATE ( SUM ( 'your ytd table'[your ytd column] ) / MAX ( Dates[Date] ) )

If the above ones can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[kalkhudary]

Hello Ying, 

thanks alot for your help but this dax equation did not work in this context. I have attached for your the sample visual and metrics I am looking at as I did not find any option to attach a pbi file. So I am looking to get the % YTD change and last year index as well ( the orange boxes). Numbers are wrong now, I demoed them as example result.

When I click on a particular point on the visual line, I want to see the current price index, last year index, last quarter % change, last year % change and most importantly YTD% change index. I am looking to write a dax syntax that would yield the YTD% change index and the last year index seperately. So when you click on a particular point in the visual, they will all show.

Is this doable?

[Anonymous]

Hi @kalkhudary ,

You can paste your data with Text format in your reply directly. Or you can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards