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.
Hi
Let's assume that we are on 06/02/2018 and we have the sales from 01/01/2017 until 06/02/2018.
I would like to compare on a matrix the turnover done each year from 01/01 until 06/02.
It has to be a matrix to drill down per store.
By default, on February 2017, it includes sales from 01/02/2017 until 28/01/2017.
I tried to create a calendar table where I added SAMEPERIODLASTYEAR but it doesnt work.
Any solution please ?
here is the pbi.file
Solved! Go to Solution.
I ve been inspired by your solution.
The aim was to use a slicer with a range.
Therefore, I used a very very very primitve solution (i.e ugly and uncomfy for final user but effective).
The cons: It's not dynamic. The user has to figure out about the date limit to get the YTD and to conceptualize it (1rst February => 0201)
I created a column which concatenate Month & Day on Query Editor.
= Table.AddColumn(#"Source", "MMDD", each Date.ToText([Date], "MM")& Date.ToText([Date], "dd"))
Hi @Anonymous,
Have you solved the problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards
Cherry
Hi,
Sales from Feb 1 to Feb 6 2017 is 167.1. You may download my PBI file from here. I have also solved a similar problem here - Show sales only for corresponding months in prior years.
Hope this helps.
I ve been inspired by your solution.
The aim was to use a slicer with a range.
Therefore, I used a very very very primitve solution (i.e ugly and uncomfy for final user but effective).
The cons: It's not dynamic. The user has to figure out about the date limit to get the YTD and to conceptualize it (1rst February => 0201)
I created a column which concatenate Month & Day on Query Editor.
= Table.AddColumn(#"Source", "MMDD", each Date.ToText([Date], "MM")& Date.ToText([Date], "dd"))
Hi @Anonymous,
What about the measure below?
Measure = VAR year = MAX ( 'Calendar'[Year] ) RETURN CALCULATE ( SUM ( 'Sales'[Turnover] ), DATESBETWEEN ( 'Calendar'[Date], DATE ( year, 1, 1 ), DATE ( year, 2, 6 ) ), FILTER ( 'Calendar', YEAR ( 'Calendar'[Date] ) = year ) )
Here is the output.
If you still need help, please share your desired output so that we could help further on it.
Best Regards,
Cherry
Unfortunately, your solution forces the user to verify the DAX formula and to refresh it at each update 😞
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |