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.
Dear Community,
I would like to create a weeknumber for specific date range in 2 years :
Example :
Start Date : 01/06/2019 to End Date : 05/11/2020
01/06/2019 to 07/06/2019 is Week1
08/06/2019 to 15/06/2019 is Week 2
......
XX/XX/2020 to XX/XX/2020 Week 145
My purpose is to create a weekly LINE chart on these specific dates for CY and LY (for same dates but 01/06/2018 to 05/011/2019).
Thanks in advance.
Solved! Go to Solution.
Like
Week = format([Week Start date],"dd/mm/YYYY") & " to "& format([Week End date],"dd/mm/YYYY")
If you need set that as date format : https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...
Check out Sequential - https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116
@Greg_Deckler Looks like that i need sequential solution.
With this calendar table is possible to create sequential measure / column ?
Create in your date table like
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date])+1
Week End date = DATEADD('Date'[Date],7-1*WEEKDAY('Date'[Date]),DAY)
Week = [Week Start date] & " to "& [Week End date]
Also refer : https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
use format on dates if needed
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/
@amitchandak Thank you for your effort.
There is a small progress but still not completed. These weeks are not sorted on my graph.
So is there any week to sort it with correct dates ?
Thanks in advance!!!
@SpiroswayGR , create week start as your sort column
@amitchandak Thanks very much!!
Almost complete my solution but still 2 remaining things...
1st : Is it possible to show dates as "DD/MM/YYYY" instead of "MM/DD/YYYY"?
2nd : Is it possible to visualize on same weekly graph metrics for this week year and metrics for Last week year ?
for example : Week for this year : 10/02/2019 to 17/02/2019 with Sum metric = 1000 and last year metric : 10/02/2018 to 17/02/2019 with sum metric : 500
Thanks again!!
Like
Week = format([Week Start date],"dd/mm/YYYY") & " to "& format([Week End date],"dd/mm/YYYY")
If you need set that as date format : https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |