Data Mapping

[Anonymous]

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).

[amitchandak]

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]

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]

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.