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 S...
Need Help
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.