Forum Discussion
Sales Comparison over different date ranges
I currently use Previous year calculation using DAX function on date ranges based on the selected date range of invoice date on the slicer. an example below is
PY SHIPPED QUANTITY ECOM = CALCULATE(SUM('PWRBI_SALES'[QUANTITY SHIPPED]), SAMEPERIODLASTYEAR(DATEADD('INVOICE DATE'[INVOICE DATE].[Date],1,DAY)))
This will return the correct values for the date ranges in the slicer. The request from our users is that they want to select a date range for the PY calculation. This way, they can compare a date range to a completely different user selected date range. They showed this functionality in google analytics page. Is there a way to have the user enter a date range and calculate the PY values based on their selection either in a slicer or in a table.
example Sales will be shown for the date range of 7/6/2019 to 7/12/2019 and the user wants to compare that to 1/6/2019 to 1/12/2019
Please provide some guidance
4 Replies
IamnvtContinued Contributor
Hi,
You need to have 2 different calendars: 1 for current selection, 1 for previous selection.
Then you can create 2 different measures based on the selection of the calendars to compare.I tried two calendars and it didnt work, Please explain your implementation process.
When i add two calendars and attach them to the invoice date in the data model, the slicer will not show any data when there is two different date ranges. I need more clarity on how you will implement two calendars with slicer and data modal.
Thanks
- Iamnvt
hi,
Current calendar has the relationship
Previous selection calendar has no relationship. It is a disconnected table.
you write the 2nd measure to calculate the previous sales, based on the selected values in the 2nd calendar.
