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.
Hello,
Please help me on the following
I have many filters like Region, Territory, Product and Year. On checking year, I have to update the checked year YTD and checked Previous year YTD.
Can you help me with that ?
Solved! Go to Solution.
My Issue is Solved in another ticket.
https://community.powerbi.com/t5/Desktop/Not-getting-the-YTD-of-previous-year-using-measures/m-p/940...
Thank you all for the support.
In case you have date table and the year is coming from that. You can have measure like these
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete 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))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin
Hey @amitchandak ,
I am not using any date table as I am connecting 3 tables and each table has region, Territory and Product to be filtered.
For Example, each date will have multiple products/Region/Territory. But in the DateTable, we can have only one date.
in my table
6th March 2019 - Central Region
6th March 2019 - North East Region
6th March 2019 - South Region
in date table, I can't have 3 rows with same date right ?
Let me know if I can do anything else.
Hi,
What's your relationships and linked columns among your three tables?
I advise creating a dimension date table to filter these tables with one-to-many relationship.
Please create a date-slicer-table like this:
DateSlicerTable = DISTINCT(SELECTCOLUMNS('Table',"Date",'Table'[Date]))
Best Regards,
Giotto Zhi
My Issue is Solved in another ticket.
https://community.powerbi.com/t5/Desktop/Not-getting-the-YTD-of-previous-year-using-measures/m-p/940...
Thank you all for the support.
Date table will have only date and date related columns like the year , month, qtr, etc. Why we need a region in that. that should be part of a different dimension.
Refer this file we have date and other common dim between few tables :https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |