Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Data Mapping

I have two tables in Analysis services. one is date table which has (Date, Month, Year, Year of Month)

01/01/2013 1 2013 20131

01/02/2013  1 2013 20131

01/03/2013 1 2013 20132 unil 2020 Dec

and ShipDates table which has Shipdays, Year of Month.

201801 ,20,

201802,21

2018,22 until 202012

I need to calculate the YTD for ship dates by applying the filter on the Date table fields (Year, Month). I joined the ship dates table and the Date table. However, it's not working it. What is the best approach to join these two tables?

I need to calculate the Shipdates (YTD) like below.

Example when select silcer as Year :2019, Month 09 from date table

  end result should be 201909- 191(Cumulative of the Months from shipdates).

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    You should try datesytd and totalytd.  Typically when you select something from date table, it should become a filter to calculate ytd

    Refer examples

    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
    Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

    Connect on Linkedin

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks, Amit. I was looking for mapping between two tables.

      I have created a static table in the Database and pulled to analysis services, and then trying to calculate the YTD ship dates based on Year, Month selection from Date table. However, the cumulative or YTD is not working on Shipdays table for shipdays.

      I have a table (ship days) like below

      Year of Month(int), ShipDays(Int)

      201801,20

      201802,21

      until

      202012,20

       

      I am not sure what went wrong in this scenario? what is the best approach to join these two tables in calculating the ship days

       

      • v-diye-msft's avatar
        v-diye-msft
        Community Support
        6 years ago

        Hi Anonymous 

         

        You might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. Please do not forget to describe the expected results based on this sample data.