Forum Discussion

kalkhudary's avatar
kalkhudary
Icon for Helper IV rankHelper IV
3 years ago

YTD

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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      kalkhudary
      Icon for Helper IV rankHelper IV
      3 years ago

      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's avatar
        Anonymous
        Not applicable
        3 years ago

        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

  • kalkhudary's avatar
    kalkhudary
    Icon for Helper IV rankHelper IV
    3 years ago

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

    DateGeographyNew Housing & Residential TypePrice Index Value 
    2017-01Calgary, AlbertaHouse only99.8
    2017-01Calgary, AlbertaLand only100
    2017-01Calgary, AlbertaCondo Only100.9
    2017-04Calgary, AlbertaHouse only99.8
    2017-04Calgary, AlbertaLand only100
    2017-04Calgary, AlbertaCondo Only102.4
    2017-07Calgary, AlbertaHouse only100.2
    2017-07Calgary, AlbertaLand only100.3
    2017-07Calgary, AlbertaCondo Only99.5
    2017-10Calgary, AlbertaHouse only100.2
    2017-10Calgary, AlbertaLand only100.5
    2017-10Calgary, AlbertaCondo Only97.1
    2018-01Calgary, AlbertaHouse only100.1
    2018-01Calgary, AlbertaLand only100.7
    2018-01Calgary, AlbertaCondo Only95.9
    2018-04Calgary, AlbertaHouse only98.9
    2018-04Calgary, AlbertaLand only100.6
    2018-04Calgary, AlbertaCondo Only92.3
    2018-07Calgary, AlbertaHouse only99.5
    2018-07Calgary, AlbertaLand only100.8
    2018-07Calgary, AlbertaCondo Only92.6
    2018-10Calgary, AlbertaHouse only98.3
    2018-10Calgary, AlbertaLand only100.4
    2018-10Calgary, AlbertaCondo Only89
    2019-01Calgary, AlbertaHouse only98.6
    2019-01Calgary, AlbertaLand only100.2
    2019-01Calgary, AlbertaCondo Only86
    2019-04Calgary, AlbertaHouse only97.5
    2019-04Calgary, AlbertaLand only100.2
    2019-04Calgary, AlbertaCondo Only84.4
    2019-07Calgary, AlbertaHouse only95.8
    2019-07Calgary, AlbertaLand only100.1
    2019-07Calgary, AlbertaCondo Only82.9
    2019-10Calgary, AlbertaHouse only95.3
    2019-10Calgary, AlbertaLand only99.9
    2019-10Calgary, AlbertaCondo Only79.8
    2020-01Calgary, AlbertaHouse only95
    2020-01Calgary, AlbertaLand only100.1
    2020-01Calgary, AlbertaCondo Only78
    2020-04Calgary, AlbertaHouse only94.5
    2020-04Calgary, AlbertaLand only99.7
    2020-04Calgary, AlbertaCondo Only78
    2020-07Calgary, AlbertaHouse only93.5
    2020-07Calgary, AlbertaLand only99.7
    2020-07Calgary, AlbertaCondo Only77.5
    2020-10Calgary, AlbertaHouse only94.4
    2020-10Calgary, AlbertaLand only100.3
    2020-10Calgary, AlbertaCondo Only78.6
    2021-01Calgary, AlbertaHouse only95.5
    2021-01Calgary, AlbertaLand only100.1
    2021-01Calgary, AlbertaCondo Only78.7
    2021-04Calgary, AlbertaHouse only101.6
    2021-04Calgary, AlbertaLand only100.1
    2021-04Calgary, AlbertaCondo Only79.4
    2021-07Calgary, AlbertaHouse only110.8
    2021-07Calgary, AlbertaLand only103.4
    2021-07Calgary, AlbertaCondo Only80.5
    2021-10Calgary, AlbertaHouse only111.6
    2021-10Calgary, AlbertaLand only103.2
    2021-10Calgary, AlbertaCondo Only80.9
    2022-01Calgary, AlbertaHouse only116.1
    2022-01Calgary, AlbertaLand only104.3
    2022-01Calgary, AlbertaCondo Only80.9
    2022-04Calgary, AlbertaHouse only131.1
    2022-04Calgary, AlbertaLand only107.3
    2022-04Calgary, AlbertaCondo Only83.4