Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |